t-sql start position of number value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

t-sql start position of number value

Author

Topic

scamper
Yak Posting Veteran

52 Posts

Posted - 2014-05-02 : 14:15:20

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?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-02 : 14:37:02

Here is a sample. This probably doesn't cover all your cases (like when a string doesn't contain a number). But, maybe it'll get you started:

DECLARE @FOO VARCHAR(100) = 'xxIN187 13-14 W Elem'

SELECT SUBSTRING(@Foo, PATINDEX('%[0-9][0-9][0-9]%', @Foo), 3)

scamper
Yak Posting Veteran

52 Posts

Posted - 2014-05-02 : 15:28:27

I tried the sql you suggested and I am getting the error message, "Msg 8116, Level 16, State 1, Line 1
Argument data type int is invalid for argument 2 of patindex function.".

Thus can you tell me what to change? Do I need to convert an int to a character value?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-02 : 15:38:19

You get an error when you try with your data or with the sample I posted? If it is with your query, can you post it?

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-05-02 : 17:20:09

[code]

SELECT CalendarId, SUBSTRING(CalendarId, PATINDEX('%[0-9][0-9][0-9]%', CalendarId + '-000'), 3)
FROM (
SELECT 'xxIN187 13-14 W Elem' AS 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' UNION ALL
SELECT 'uh oh 11 no 22 3 digit number string found 45'
) AS test_data

[/code]

scamper
Yak Posting Veteran

52 Posts

Posted - 2014-05-02 : 17:27:15

The very last answer soled the problem. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources