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?-SergioI 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)-SergioI 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 tooSELECT PARSENAME(REPLACE(REPLACE(REPLACE(column,'{',''),'}',''),':','.'),1) AS IntegerValueFROM table ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 @tabVALUES ('{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 @tabP.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.-SergioI 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.-SergioI 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|