Extract INT from string? - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Extract INT from string?

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2014-02-19 : 17:05:48

I have a table which has important information stored within a text field as nText. The fields look like this

{B:P:8}
{B:D:18}
{A:P:821}
{E:D:38}
{A:D:9}
{B:D:18}

The integer after the last colon & before the curly brace is the one I need to extract. Is there a way to do this in SQL?

I tried this at first, but the problem is that it's not very tolerant to the varying lengths of the integer.

,RIGHT(LEFT(CAST(bN.Note AS NVARCHAR(50)),6),2)

Any ideas?

-Sergio
I use Microsoft SQL 2008

SergioM
Posting Yak Master

170 Posts

Posted - 2014-02-19 : 17:59:10

Resolved.

I just discovered Pattern Index (PATINDEX). Seems to do the job well!

,LEFT(RIGHT(CAST(bN.Note AS NVARCHAR(50)),PATINDEX('%[0-9999]%',bN.Note)-3),1)

-Sergio
I use Microsoft SQL 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-20 : 00:29:40

if its always three parts you've you can do like this too


SELECT PARSENAME(REPLACE(REPLACE(REPLACE(column,'{',''),'}',''),':','.'),1) AS IntegerValue
FROM table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2014-02-20 : 02:47:43

This is also another way to do please find it

DECLARE @tab TABLE (
NAME VARCHAR(20)
)

INSERT INTO @tab
VALUES ('{B:P:8}'),
('{B:D:18}'),
('{A:P:821}'),
('{E:D:38}'),
('{A:D:9}'),
('{B:D:18}')
Select REPLACE(SubString(NAME,PATINDEX('%[0-9]%',NAME),Len(NAME)),'}','') FROM @tab

P.V.P.MOhan

SergioM
Posting Yak Master

170 Posts

Posted - 2014-02-20 : 12:29:04

Great, thanks! I also need to extract the first & second integer, which I was going to do with LEFT/RIGHT, but I suppose either of your methods are more reliable.

-Sergio
I use Microsoft SQL 2008

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-22 : 09:30:00

[code]SELECT Name,
REVERSE(SUBSTRING(REVERSE(Name), 2, CHARINDEX(':', REVERSE(Name)) - 2))
FROM @Tab;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-23 : 02:53:46

quote:
Originally posted by SergioM

Great, thanks! I also need to extract the first & second integer, which I was going to do with LEFT/RIGHT, but I suppose either of your methods are more reliable.

-Sergio
I use Microsoft SQL 2008


SELECT PARSENAME(REPLACE(REPLACE(REPLACE(column,'{',''),'}',''),':','.'),1) AS LastInteger,
PARSENAME(REPLACE(REPLACE(REPLACE(column,'{',''),'}',''),':','.'),2) AS [2ndInteger],
PARSENAME(REPLACE(REPLACE(REPLACE(column,'{',''),'}',''),':','.'),3) AS [FirstInteger]
FROM table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources