Padding with leading 0's trying to account for alpha character at end 2

[bmacbmac]

Hello,

I have numbers in a column:
1
5
9
10
15A
19
20
100
125

I am trying to pad leading 0's so the numerical part of each entry is 3 digits. If I just pad leading 0's I get this:

001
005
009
010
019
020
100
125
15A

By doing so, the 15A is out of order when sorting. The code I am running is:

select RIGHT('000' + convert(varchar(3),mytable.COL001), 3) from mytable

Is there a way I can have SQL recognize the 15 part of the number to pad it with a 0 so it falls in line as 015A?

This is SQL 2000.

Thanks in advance!

 

[markros]

Don't see a simple way except for getting numbers from the string first - which is a bit tricky in itself - George has a blog on this problem.

 

[markros]

If you always may have only one character at the end, then you may try to use case as

case when ISALPHA(right(myField,1))=1 then RIGHT('000' + convert(varchar(3),substring(mytable.COL001,1,len(myTable,Col001)-1)), 3) else ... end

 

[bborissov]

Not tested:

[COLOR=blue]DECLARE[/color] @Test [COLOR=blue]TABLE[/color] (Fld1 [COLOR=blue]varchar[/color](20))
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @Test [COLOR=blue]values[/color] ([COLOR=red]'1'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @Test [COLOR=blue]values[/color] ([COLOR=red]'5'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @Test [COLOR=blue]values[/color] ([COLOR=red]'9'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @Test [COLOR=blue]values[/color] ([COLOR=red]'10'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @Test [COLOR=blue]values[/color] ([COLOR=red]'15A'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @Test [COLOR=blue]values[/color] ([COLOR=red]'19'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @Test [COLOR=blue]values[/color] ([COLOR=red]'20'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @Test [COLOR=blue]values[/color] ([COLOR=red]'100'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @Test [COLOR=blue]values[/color] ([COLOR=red]'125'[/color])

[COLOR=blue]select[/color] Fld1, [COLOR=#FF00FF]RIGHT[/color]([COLOR=red]'000'[/color]+[COLOR=#FF00FF]SUBSTRING[/color](Fld1,1, [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] [COLOR=#FF00FF]PATINDEX[/color]([COLOR=red]'%[^0-9]%'[/color], Fld1)=0
                                                   [COLOR=blue]THEN[/color] 8000
                                             [COLOR=blue]ELSE[/color] [COLOR=#FF00FF]PATINDEX[/color]([COLOR=red]'%[^0-9]%'[/color], Fld1)-1 [COLOR=blue]END[/color]), 3) [COLOR=blue]AS[/color] TestMe
[COLOR=blue]FROM[/color] @Test 
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] TestMe

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

markros,
ISALPHA()?
In T-SQL
I think you mess it up with other language

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Good point! I was sure we have ISALPHA() since we have ISNUMERIC(), but I just checked and there is no such function

Your solution works great, I've tested it on the sample you provided.

 

[bmacbmac]

Thanks bborissov! It did recognize the numeric portion of 15A and made it 015. Now I just need to figure out how to add the A back on at the end so I have:
015
015A
015B

Instead of what I originally had as:
015
15A
15B

 

[bborissov]

Better say that at once
You could separate the numeric part in one column and the rest in other or use something like this:

[COLOR=blue]DECLARE[/color] @Test [COLOR=blue]TABLE[/color] (Fld1 [COLOR=blue]varchar[/color](20))
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @Test [COLOR=blue]values[/color] ([COLOR=red]'1'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @Test [COLOR=blue]values[/color] ([COLOR=red]'5'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @Test [COLOR=blue]values[/color] ([COLOR=red]'9'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @Test [COLOR=blue]values[/color] ([COLOR=red]'10'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @Test [COLOR=blue]values[/color] ([COLOR=red]'15A'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @Test [COLOR=blue]values[/color] ([COLOR=red]'19'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @Test [COLOR=blue]values[/color] ([COLOR=red]'20'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @Test [COLOR=blue]values[/color] ([COLOR=red]'100'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @Test [COLOR=blue]values[/color] ([COLOR=red]'15C'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @Test [COLOR=blue]values[/color] ([COLOR=red]'125'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @Test [COLOR=blue]values[/color] ([COLOR=red]'15B'[/color])


[COLOR=blue]select[/color] Fld1, [COLOR=#FF00FF]RIGHT[/color]([COLOR=red]'000'[/color] + [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] [COLOR=#FF00FF]PATINDEX[/color]([COLOR=red]'%[^0-9]%'[/color], Fld1) = 0
                                     [COLOR=blue]THEN[/color] Fld1
                                [COLOR=blue]ELSE[/color] [COLOR=#FF00FF]SUBSTRING[/color](Fld1,1, [COLOR=#FF00FF]PATINDEX[/color]([COLOR=red]'%[^0-9]%'[/color], Fld1)-1)
                           [COLOR=blue]END[/color], 3)+
             [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] [COLOR=#FF00FF]PATINDEX[/color]([COLOR=red]'%[^0-9]%'[/color], Fld1) = 0
                       [COLOR=blue]THEN[/color] [COLOR=red]''[/color]
                  [COLOR=blue]ELSE[/color] [COLOR=#FF00FF]SUBSTRING[/color](Fld1,[COLOR=#FF00FF]PATINDEX[/color]([COLOR=red]'%[^0-9]%'[/color], Fld1),8000)
              [COLOR=blue]END[/color] [COLOR=blue]AS[/color] TestMe
[COLOR=blue]FROM[/color] @Test
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] TestMe

I didn't say it is un-tested, just because markros tested previous one ;-)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bmacbmac]

Yes! That worked perfectly. I really really really appreciate it.

 

[markros]

The simple way to show your appreciation is to click on Thank bborisov for this valuable post link at the bottom of the message in addition to the Thank you message.