SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Extract INT from string?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SergioM
Posting Yak Master

144 Posts

Posted - 02/19/2014 :  17:05:48  Show Profile  Reply with Quote
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

144 Posts

Posted - 02/19/2014 :  17:59:10  Show Profile  Reply with Quote
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

Edited by - SergioM on 02/19/2014 18:55:18
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/20/2014 :  00:29:40  Show Profile  Reply with Quote
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
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 02/20/2014 :  02:47:43  Show Profile  Reply with Quote
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
Go to Top of Page

SergioM
Posting Yak Master

144 Posts

Posted - 02/20/2014 :  12:29:04  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30106 Posts

Posted - 02/22/2014 :  09:30:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT	Name,
	REVERSE(SUBSTRING(REVERSE(Name), 2, CHARINDEX(':', REVERSE(Name)) - 2))
FROM	@Tab;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/23/2014 :  02:53:46  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000