[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 463
  • Last Modified:

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?
0
jmp02008
Asked:
jmp02008
  • 2
1 Solution
 
ChrisKingCommented:
1> declare @var varchar(20)
2> select @var = 'M000000113'
3> select substring(@var,1,patindex('%[0-9]%',@var)-1) + convert(varchar(20),convert(numeric(20),substring(@var,1+patindex('%[0-9]%',@var)-1,20)))
4>
5> select @var = '0000018759'
6> select substring(@var,1,patindex('%[0-9]%',@var)-1) + convert(varchar(20),convert(numeric(20),substring(@var,1+patindex('%[0-9]%',@var)-1,20)))
go
7> (1 row affected)

 ----------------------------------------
 M113

(1 row affected)
(1 row affected)

 ----------------------------------------
 18759

(1 row affected)
1>
0
 
ChrisKingCommented:
1> create table #test(oldvalue varchar(20))
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,patindex('%[0-9]%',oldvalue)-1)
10>                 + convert(varchar(20),convert(numeric(20),substring(oldvalue,1+patindex('%[0-9]%',oldvalue)-1,20)))
11> from   #test
12> go
 oldvalue             newvalue
 -------------------- ----------------------------------------
 M000000113           M113
 M000001194           M1194
 M000000245           M245
 0000018759           18759
 0000011011           11011

(5 rows affected)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now