jmp02008
asked on
String selection charindex
If i have some values that look like this:
M000000113
M000001194
M000000245
0000018759
0000011011
I want to select these values but make this number M000000113 look like this M113 and numbers like this 0000011011 look like 11011.
Bassically get rid of leading 0s and all 0s in between the M and the number. Any suggestions?
M000000113
M000001194
M000000245
0000018759
0000011011
I want to select these values but make this number M000000113 look like this M113 and numbers like this 0000011011 look like 11011.
Bassically get rid of leading 0s and all 0s in between the M and the number. Any suggestions?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
2> insert into #test select 'M000000113'
3> insert into #test select 'M000001194'
4> insert into #test select 'M000000245'
5> insert into #test select '0000018759'
6> insert into #test select '0000011011'
7>
8> select oldvalue
9> , newvalue = substring(oldvalue,1,patin
10> + convert(varchar(20),conver
11> from #test
12> go
oldvalue newvalue
-------------------- --------------------------
M000000113 M113
M000001194 M1194
M000000245 M245
0000018759 18759
0000011011 11011
(5 rows affected)