Find first non-numeric Charater in a string

Question

Find first non-numeric Charater in a string

Christopher Stobbs

SSC-Insane

Points: 21098
More actions
April 29, 2008 at 11:49 am

#206816

HI All,
I had this question asked to me today and still working on it.
If you have a table, with one col called RefNum
How do you return the the following:
Refnum
,(First non-numeric character in Refnum)
Ideally I'd like some set based code on this instead of some hectic loops etc.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
SQL-4-Life

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Assuming you mean "not 0 through 9" when you say non-numeric (i.e. not trying to figure out decimal points, signs, etc...), then

substring(refnum,patindex('%[^1-9]%',Refnum),1)

should do the trick.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 2

Matt... shouldn't that be 0-9?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Jeff Moden (4/29/2008)
Matt... shouldn't that be 0-9?

I knew there was something missing there......:blush:

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

SqlUser-529296 SSCarpal Tunnel Points: 4072 More actions · Answer 4

Hi Stobbs,

Try this one.

DECLARE @refnum VARCHAR(128)

SET @refnum = '2143345345345345345345345342100712n3456789090987766544332211m'

SELECT SUBSTRING(@refnum,PATINDEX('%[a-z]%',@refnum),1)

---:)

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 5

hi Guys,

Thanks all for the reply 🙂

I feel like an idiot, I totally forgot about the PatIndex function he he he

Thanks again

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life