Select First X Number of Digits from Column

Transact-SQL
1

Hi experts,
After googling all morning, I have not found an adequate solution, so I'm asking for help.

The Comm column is defined as varchar.
Objective: Starting at the left-most position 1, select contiguous digits until a non-numeric char is found, stop there.

This is my sample data in a column named Comm

image

Thanks

2 

SELECT LEFT(Comm, PATINDEX('%[^0-9]%', Comm + '.') - 1)
2 Likes
3

This is exactly what I needed, @ScottPletcher. I was trying to use a Substring function in conjunction with PATINDEX. Thanks for your tip.

4

You're welcome.

Btw, the " + '.' " part is so that a Comm value like just:
1234
will work correctly.

5

hi

3 different ways .. different from Scotts

Just exercising my noodles .. please excuse

create data script

drop table if exists #Temp
create table #Temp( CommValue varchar(20))
insert into #Temp select '13300'
insert into #Temp select '13120-06'
insert into #Temp select '88160 sub 9'
insert into #Temp select '49210-A'
insert into #Temp select '15420'
insert into #Temp select 'Class 400.0'

-- Using Translate

select 
     REPLACE(TRANSLATE(CommValue, 'abcdefghijklmnopqrstuvwxyz+()- ,#+', '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'), '@', '')
from 
   #Temp  
WHERE 
   CommValue LIKE '[0-9]%'

-- Using Stuff , patindex

select 
     stuff(CommValue+'.',PATINDEX('%[^0-9]%',CommValue+'.'),100,'' )  
from 
   #Temp
WHERE 
   CommValue LIKE '[0-9]%'

-- using substring , patindex

select 
    substring(CommValue,PATINDEX('[0-9]%',CommValue+'.'),PATINDEX('%[^0-9]%',CommValue+'.')-1) 
from 
    #Temp
WHERE 
   CommValue LIKE '[0-9]%'

image

6

hi

a fourth way of doing it .. its complicated .. i am aware of ISNUMERIC issues

.. but my idea was to

exercise my noodles
neuroscience
browsing the internet
look for code
modify it and get my solution
and lots of other stats etc etc etc which you may not understand like cognition , meta cognition

please excuse me

-- using tally table , row number , traversing the string one character at a time

; with rn_cte    as ( select ROW_NUMBER() over(order by CommValue) as rn , * from #Temp ) 
,      tally_cte as ( select N=number from master..spt_values where type = 'P' )
SELECT 
    rn
  , max(left(f.CommValue, x.n))
FROM 
    tally_cte x 
      INNER JOIN 
	rn_cte AS f
ON 
  x.n <= LEN(f.CommValue)
WHERE 
    ISNUMERIC(left(f.CommValue, x.n)) = 1 
GROUP BY 
  rn

image
image651×534 8.43 KB