oracle数据库中SQL的lpad函数实现补零操作

2012年02月24日 其他资源 18条评论 阅读17100次

今天工作上遇到这样一个问题。是oracle数据库中SQL实现补零操作的问题。、

以下是当时问题的环境还原。

oracle11g下有一数据表、表名A

A表中有CD1 ,CD2两个字段,CD2字段最大字符长度是5.

现客户要求检索出A表的所有数据,并要求使用CD2字段升序排列。

本来很简单的一个sql就搞定,但偏偏CD2是个很特殊的字段,该字段被定义成varchar2类型,但数据却大部分存储的是正整数数字,在下述情况中,排序就有问题了

CD2字段值为1,2,11,22.  如果简单使用order by CD2。得到的结果将会是1,11,2,22

但客户希望的结果是1,2,11,22  。。

要达到上述结果,很容易让人想到的是利用to_number()函数把CD2字段的值转换成为数字类型。

很遗憾的是,前面也有所提到,CD2字段也有可能会有子母的存在,如果对字母也使用to_number()函数,那么sql语句将会发生内部错误而无法执行。

到这里 就想到先把CD2字段进行补位操作,因为CD2最大字符长度是5位,所以,一个将CD2字段补零使其满5位再进行排序的思路就出来了。

在网络上搜索补位操作,看到了lpad函数。lpad( 要补位的字符, 要补满的字符长度, 使用什么字符来补位)

基于以上语法,得到以下sql语句

select *

from A

order by lpad(CD2,5,'0')

执行,果然,出效果了。

看来还是lpad函数帮了大忙。

 

另外,在简单说一下lpad函数。

lpad不仅能补位,还能截位。

例如:

 

  SQL> select lpad('abcde',2) from dual;

 

  LPAD('ABCDE',2) 将得到ab的结果。说明lpad函数截取了'abcde'的从 数起前两位

 

  与lpad函数对应的是rpad函数:

 

  rpad函数从右边对字符串使用指定的字符进行填充,语法格式与lpad格式相同:

 

  rpad(string,padded_length,[pad_string])

以上。在实际项目需要时,可以更加灵活使用。

分享本文至:

WRITTEN BY

avatar
本文标签:oraclesqllpad
看了本文是不是觉得很赞,那就赶紧点击下面按钮分享给身边的朋友吧!

18 条评论

  1. <a href="http://www.seymourdugan.com/mennns-nike-flyknit-lunar-3-lilla-rosa-obuvi">mennns nike flyknit lunar 3 lilla rosa</a> <a href="http://www.dkstekstil.com/air-jordan-retro-14-mujeres-rojo-p%C3%BArpura-zapatosp">air jordan retro 14 mujeres rojo p煤rpura</a> <a href="http://www.thegillssell.com/nike-roshe-two-flyknit-lilla-bl%C3%A5-skol">nike roshe two flyknit lilla bl氓</a>
    veskopetrov http://www.veskopetrov.com/

  2. <a href="http://www.ebooksandme.com/nike-air-max-90-tout-blanc-junior-butyg">nike air max 90 tout blanc junior</a> <a href="http://www.fresnotxseo.com/nike-air-huarache-blau-orange-scarpea">nike air huarache blau orange</a> <a href="http://www.itsanomadlife.com/nike-flyknit-air-vapormax-2018-sort-lyser%C3%B8d-schuhec">nike flyknit air vapormax 2018 sort lyser酶d</a> <a href="http://www.myfoxbeaumont.com/adidas-climachill-rocket-boost-svart-berry-butyf">adidas climachill rocket boost svart berry</a> <a href="http://www.roidezign.com/adidas-superstar-xeno-svart-hvit-butyf">adidas superstar xeno svart hvit</a> <a href="http://www.terrysammons.com/air-max-tailwind-6-schwarz-gelb-obuvf">air max tailwind 6 schwarz gelb</a>
    pmtraincert http://www.pmtraincert.com/

  3. <a href="http://www.munchstrom.net/hommes-nike-air-max-tn-vert-jaune-schuhed">hommes nike air max tn vert jaune</a> <a href="http://www.mycouponexpert.net/adidas-springblade-ignite-verde-p%C3%BArpura-butye">adidas springblade ignite verde p煤rpura</a> <a href="http://www.mydxp.net/air-jordan-reveal-blau-top-obuvd">air jordan reveal blau top</a> <a href="http://www.myersmotors.net/nike-kobe-9-r%C3%B8d-mamba-located-obuvg">nike kobe 9 r酶d mamba located</a> <a href="http://www.mysaltwatersandal.net/adidas-harden-amarillo-azul-schuhef">adidas harden amarillo azul</a> <a href="http://www.nadinesproperties.net/air-jordan-retro-11-oransje-gul-obuve">air jordan retro 11 oransje gul</a>
    firstcaldealworks http://www.firstcaldealworks.net/

  4. <a href="http://www.divorcecpas.com/adidas-tubular-runner-heather-gr%C3%A5-alleure-chaussuresf">adidas tubular runner heather gr氓 alleure</a> <a href="http://www.donhowsuccess.com/nike-huarache-ultra-marron-violet-scarpef">nike huarache ultra marron violet</a> <a href="http://www.emtekmakina.com/paris-saintgermain-3-way-fidget-spinner-a128red-nfls">paris saintgermain 3 way fidget spinner a128red</a> <a href="http://www.kokismith.com/ralph-lauren-femmes-blanc-polo-suit-polot">ralph lauren femmes blanc polo suit</a> <a href="http://www.luftsepp.com/womens-jalen-mills-limited-olivecamo-nike-jersey-nfl-philadelphia-eagles-31-2017-salute-to-service-nflz">womens jalen mills limited olivecamo nike jersey nfl philadelphia eagles 31 2017 salute to service</a> <a href="http://www.rimsanmakine.com/nike-zoom-pegasus-33-mujeres-azul-rojo-kurpesc">nike zoom pegasus 33 mujeres azul rojo</a>
    tryautolikes http://www.tryautolikes.com/

  5. <a href="http://www.myenergytruth.com/nike-womens-nick-nelson-limited-olive-jersey-oakland-raiders-nfl-38-2017-salute-to-service-nflw">nike womens nick nelson limited olive jersey oakland raiders nfl 38 2017 salute to service</a> <a href="http://www.sosalito.com/schwarz-and-wei%C3%9F-jordan-13-schuheg">schwarz and wei脽 jordan 13</a> <a href="http://www.stretart.com/herren-nike-air-max-tn-gr%C3%BCn-rot-scarpeg">herren nike air max tn gr眉n rot</a> <a href="http://www.waardefabriek.com/nike-kvinners-lunar-force-1-sky-hi-jacquard-kurpese">nike kvinners lunar force 1 sky hi jacquard</a> <a href="http://www.agnnetworks.com/mennns-nike-kd-trey-5-ii-basketball-sko-schuheg">mennns nike kd trey 5 ii basketball sko</a> <a href="http://www.axeaffaires.com/nike-kd-n7-for-venta-houston-schuheb">nike kd n7 for venta houston</a>
    whitegayshop http://www.whitegayshop.com/

  6. <a href="http://www.georgesherry.com/jordan-eclipse-all-white-customized-sneakere">jordan eclipse all white customized</a> <a href="http://www.jenalgroup.com/kvinders-nike-lebron-13-lilla-r%C3%B8d-obuvc">kvinders nike lebron 13 lilla r酶d</a> <a href="http://www.badacamshotel.com/nike-ash-alex-okafor-backer-nfl-57-new-orleans-saints-pullover-hoodie-nflw">nike ash alex okafor backer nfl 57 new orleans saints pullover hoodie</a> <a href="http://www.compressmyjs.com/mens-under-armour-midnight-green-philadelphia-eagles-combine-authentic-lockup-tech-long-sleeve-t-shirt-nflv">mens under armour midnight green philadelphia eagles combine authentic lockup tech long sleeve t shirt</a> <a href="http://www.fetison.com/flyknit-lunar-3-pink-yellow-skog">flyknit lunar 3 pink yellow</a> <a href="http://www.neffloot.com/kobe-10-elite-pink-all-white-skob">kobe 10 elite pink all white</a>
    theclaydoctor http://www.theclaydoctor.com/

  7. <a href="http://www.flibroactiv.com/nike-huarache-hommes-vente-kurpesb">nike huarache hommes vente</a> <a href="http://www.floozyspeak.com/flyknit-trainer-alle-wei%C3%9F-jordans-schuhec">flyknit trainer alle wei脽 jordans</a> <a href="http://www.garajebeobide.com/nike-free-run-3-azul-rojo-schuheg">nike free run 3 azul rojo</a> <a href="http://www.magaview.com/nike-roshe-ld-1000-kvinders-kurpesc">nike roshe ld 1000 kvinders</a> <a href="http://www.expandam.com/nike-sb-stefan-janoski-max-rot-grau-obuvg">nike sb stefan janoski max rot grau</a> <a href="http://www.getitcargo.com/nike-roshe-run-gelb-gold-scarped">nike roshe run gelb gold</a>
    cydendressage http://www.cydendressage.com/

  8. avatar royalpinkwax

    <a href="http://www.houstonujc.com/nike-free-run-2-l%C3%B8b-sko-sort-kvinders-chaussuresa">nike free run 2 l酶b sko sort kvinders</a> <a href="http://www.jetsonpress.com/adidas-climacool-ride-vi-gr%C3%B8nn-bl%C3%A5-butya">adidas climacool ride vi gr酶nn bl氓</a> <a href="http://www.masadablog.com/adidas-zapatillas-heren-stan-smith-obuvc">adidas zapatillas heren stan smith</a> <a href="http://www.soknorealtor.com/air-max-1-blu-liberty-scarpea">air max 1 blu liberty</a> <a href="http://www.donnaemendoza.com/femmes-nike-air-max-90-bleu-rose-scarpef">femmes nike air max 90 bleu rose</a> <a href="http://www.erneyapi.com/herren-supra-skytop-rosa-himmelblau-obuvc">herren supra skytop rosa himmelblau</a>
    royalpinkwax http://www.royalpinkwax.com/

  9. avatar nawe264s

    <a href="http://www.3191jzrh.com/salomon-speedcross-3-cs-schwarz-gelb-obuvd">salomon speedcross 3 cs schwarz gelb</a> <a href="http://www.te73uevz.com/air-jordan-11-low-gs-frost-blanco-chaussuresa">air jordan 11 low gs frost blanco</a> <a href="http://www.1wk531nc.com/zx-flux-schwarz-oro-dahombres-butya">zx flux schwarz oro dahombres</a> <a href="http://www.miuwhy36.com/air-yeezy-ii-2-sp-max-90-marr%C3%B3n-p%C3%BArpura-scarpef">air yeezy ii 2 sp max 90 marr贸n p煤rpura</a> <a href="http://www.yoes39mj.com/nike-structure-20-blanco-butyf">nike structure 20 blanco</a> <a href="http://www.bm067skf.com/air-jordan-12-retro-nero-yeezy-butyf">air jordan 12 retro nero yeezy</a>
    nawe264s http://www.nawe264s.com/

  10. avatar voilahalla

    <a href="http://www.cosmonet.net/kvinners-asics-gel-noosa-tri-9-svart-hvit-schuhed">kvinners asics gel noosa tri 9 svart hvit</a> <a href="http://www.cpgjoblist.net/nike-zoom-pegasus-33-kvinders-r%C3%B8d-lyser%C3%B8d-kurpesb">nike zoom pegasus 33 kvinders r酶d lyser酶d</a> <a href="http://www.crosswindschurch.net/adidas-prossoator-verde-and-nero-kurpesa">adidas prossoator verde and nero</a> <a href="http://www.cuttingedgeoffers.net/nike-free-run-2-deep-royal-bleu-obuvb">nike free run 2 deep royal bleu</a> <a href="http://www.cvceurope.net/huarache-ultra-lilla-flipkart-kurpesa">huarache ultra lilla flipkart</a> <a href="http://www.cyberlec.net/air-jordan-super-fly-iv-jaune-violet-butyd">air jordan super fly iv jaune violet</a>
    [url=http://www.voilahalla.net/]voilahalla[/url]

  11. avatar planetmolly

    <a href="http://www.willymunoz.com/grey-prada-sunglasses-pradar">grey prada sunglasses</a> <a href="http://www.seemeonweb.com/nike-dunk-spongebob-batman-runningb">nike dunk spongebob batman</a> <a href="http://www.clojuregames.com/michael-michael-kors-medium-romy-backpack-mkr">michael michael kors medium romy backpack</a> <a href="http://www.threeoaksmi.com/billig-new-balance-990-kvinders-gr%C3%B8n-sneakere">billig new balance 990 kvinders gr酶n</a>
    planetmolly http://www.planetmolly.com/

  12. avatar 婚礼摄影MV

    婚礼摄像微电影www.5aivideo.com婚礼摄影MVwww.5aivideo.com-客服咨询QQ73595(企鹅扣扣)-Email:73595@qq.com婚礼跟拍多少钱www.5aivideo.com

  13. avatar 天津卷帘门厂

    操作很给力哈

  14. avatar 上海做网站

    谢谢分享,学习了

  15. avatar 粉丝批发

    走过路过支持一下嘿嘿

  16. avatar 刘稻SEO

    技术贴,支持!欢迎博主回访!

  17. avatar 影楼网络营销

    不错,过来学习一下了

  18. avatar 柚格利

    技术贴,学习一下

欢迎留言




用户登录

sitemap