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

Thanks

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

2 Likes

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

You're welcome.

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

1234

will work correctly.

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]%'
```

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
```