t-sql start position of number value in a string

Question

Post reply

t-sql start position of number value in a string

wendy elizabeth

SSCoach

Points: 16915
More actions
May 2, 2014 at 12:16 pm

#290958

In t-sql 2008 r2, I would like to know how to select a specific string in a varchar(50) field. The field in question is called 'CalendarId'.
This field can contain values like:
xxIN187 13-14 W Elem
HS321 13-14 D Elem
IN636 13-14 C Elem
030 13-14 clark middle.
What I am looking for is the first position that contains a number value for the length of 3. Thus what I want are values that look like the following: 030, 636, 187.
What I know that I want is substring(CalendarId,?,3).
The question mark is where I want the starting location of a number value (0 to 9) of the value in CalendarId . I tried pathindex but my syntax did not work.
Thus can you show me the t-sql that will solve my problem?

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Are you looking for something like this? Or what is your expected output?

WITH SampleData AS(

SELECT 'xxIN187 13-14 W Elem' CalendarId UNION ALL

SELECT 'HS321 13-14 D Elem' UNION ALL

SELECT 'IN636 13-14 C Elem' UNION ALL

SELECT '030 13-14 clark middle.'

)

SELECT *, PATINDEX('%[0-9][0-9][0-9]%', CalendarId)

FROM SampleData

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

djj SSCoach Points: 18831 More actions · Answer 2

I believe you are looking for CHARINDEX with [0-9][0-9][0-9].

Something like:

SUBSTRING(yourvalue, CHARINDEX('[0-9][0-9][0-9]',yourvalue), lengthyourvalue)

Note this is not tested.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

djj (5/2/2014)
I believe you are looking for CHARINDEX with [0-9][0-9][0-9].
Something like:
SUBSTRING(yourvalue, CHARINDEX('[0-9][0-9][0-9]',yourvalue), lengthyourvalue)
Note this is not tested.

CHARINDEX doesn't accept wildcards, that why we need to use PATINDEX.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

djj SSCoach Points: 18831 More actions · Answer 4

Thanks Lois. I said it was not tested. 😀

That is two wrong in about ten minutes. Time to go home.

wendy elizabeth SSCoach Points: 16915 More actions · Answer 5

When I try: PATINDEX('%[0-9][0-9][0-9]%', CalendarId), I get the error message, "Msg 8116, Level 16, State 1, Line 2

Argument data type int is invalid for argument 2 of patindex function."

Thus can you tell me what else you would try to use?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

You could start by posting the correct information. The values that you posted can't be stored in an int column. You have either the incorrect column or the incorrect logic. I can't guess on this one.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2