asked on How to return only part of a string field in sql query
Hi Experts,
I want to extract a very small part of a large nvarchar field containing legacy data in order to update a new table.
From the information in this table I wish to get, amongst other fields which are straightforward, a number that is present embedded in text in only some lines, eg:
Field [Legacy_data] contains 'a random amount of text ( 2.3)Kg another random amount of text'
I want to return only the 2.3 from withing the ()Kg and ONLY from those lines that contain '%)Kg%'. Can this be done easily?
I want to extract a very small part of a large nvarchar field containing legacy data in order to update a new table.
From the information in this table I wish to get, amongst other fields which are straightforward, a number that is present embedded in text in only some lines, eg:
Field [Legacy_data] contains 'a random amount of text ( 2.3)Kg another random amount of text'
I want to return only the 2.3 from withing the ()Kg and ONLY from those lines that contain '%)Kg%'. Can this be done easily?
Microsoft SQL Server 2005Microsoft SQL ServerMicrosoft SQL Server 2008
Last Comment
vdr1620
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thats almost perfect, only thing is if the text before the ()Kg contains "(" then I am getting a load of text returned - can I centre the extract on the ")Kg" part of text?
e.g. some amount of text (Lab results pending - blah blah weight was ( 2.3)Kg and we expect an increase
is returning:
Lab results pending - blah blah weight was ( 2.3
All other lines are fine, only those with a "(" in the previous text are wrong.
e.g. some amount of text (Lab results pending - blah blah weight was ( 2.3)Kg and we expect an increase
is returning:
Lab results pending - blah blah weight was ( 2.3
All other lines are fine, only those with a "(" in the previous text are wrong.
Try changing your pattern for PATINDEX to:
'%(%[0-9]%)Kg%'
'%(%[0-9]%)Kg%'
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
This is really really close but is still returning text up to the first "(" in the string if more than 1 is present.
Patrick's SQL seems to work..
CREATE TABLE #T
(
ColumnName Nvarchar(max)
)
INSERT INTO #T Values ('(Lab results pending - blah blah weight was (2.3)Kg and we expect an increase ')
SELECT LTRIM(RTRIM(SUBSTRING(Colu
CHARINDEX(')Kg', ColumnName, PATINDEX('%([ 0-9]%)Kg%', ColumnName)) -
PATINDEX('%([ 0-9]%)Kg%', ColumnName) - 1)))
FROM #T
WHERE ColumnName LIKE '%([ 0-9]%)Kg%'
CREATE TABLE #T
(
ColumnName Nvarchar(max)
)
INSERT INTO #T Values ('(Lab results pending - blah blah weight was (2.3)Kg and we expect an increase ')
SELECT LTRIM(RTRIM(SUBSTRING(Colu
CHARINDEX(')Kg', ColumnName, PATINDEX('%([ 0-9]%)Kg%', ColumnName)) -
PATINDEX('%([ 0-9]%)Kg%', ColumnName) - 1)))
FROM #T
WHERE ColumnName LIKE '%([ 0-9]%)Kg%'
ASKER
Yes, I tried it again and works perfectly - but if I insert this text into the table it doesn't work, is there anything in this text that would throw it out? If its just that the text is too random, I can go through and adjust manually.
Combo ( 30.08 Apply One Vial Every 1 Months To Skin Behind Nec 31-03-10 ?advise Next Time *weight Entered As ( .95)kg. Payment: Cash KG 81.8 2nd VA/Nar Offer KG
Combo ( 30.08 Apply One Vial Every 1 Months To Skin Behind Nec 31-03-10 ?advise Next Time *weight Entered As ( .95)kg. Payment: Cash KG 81.8 2nd VA/Nar Offer KG
OK, try this:
SELECT LTRIM(RTRIM(SUBSTRING(SomeColumn, PATINDEX('%([ .0-9]%)Kg%', SomeColumn) + 1,
CHARINDEX(')Kg', SomeColumn, PATINDEX('%([ .0-9]%)Kg%', SomeColumn)) -
PATINDEX('%([ .0-9]%)Kg%', SomeColumn) - 1)))
FROM SomeTable
WHERE SomeColumn LIKE '%([ .0-9]%)Kg%'
it is showing up because the SQL above tests for condition Like ( followed by numbers.. and since there are two of those kind in the Value its picking the First match i.e., ( 30.08 and then calculating the difference of length between the 1st ( bracket and ) and that's the reason you see that result instead of .95
for your ref:
http://msdn.microsoft.com/en-us/library/ms188395.aspx
for your ref:
http://msdn.microsoft.com/en-us/library/ms188395.aspx
I'm beginning to think that maybe you're better off using a .Net CLR function leveraging Regular Expressions.
Alas, I will not be able to help with that, as I am a .Net n00b.
Alas, I will not be able to help with that, as I am a .Net n00b.
ASKER
Many thanks both, I had not come across these sql functions before and now will go and learn to use them properly!
The solution was close enough to get me the info I need, I hope splitting the points between you is OK?
The solution was close enough to get me the info I need, I hope splitting the points between you is OK?
Glad to help, and no problem at all with the split :)
also.. check this.. i added some SQL to mathew's Script
SELECT CASE WHEN LEN(ColumnName) > 6 THEN LTRIM(RTRIM(SUBSTRING(Colu
ELSE ColumnName END
FROM
(
SELECT LTRIM(RTRIM(SUBSTRING(Colu
CHARINDEX(')Kg', ColumnName, PATINDEX('%([ 0-9]%)Kg%', ColumnName)) -
PATINDEX('%([ 0-9]%)Kg%', ColumnName) - 1))) as ColumnName
FROM #T
WHERE ColumnName LIKE '%([ 0-9]%)Kg%'
)derived
SELECT CASE WHEN LEN(ColumnName) > 6 THEN LTRIM(RTRIM(SUBSTRING(Colu
ELSE ColumnName END
FROM
(
SELECT LTRIM(RTRIM(SUBSTRING(Colu
CHARINDEX(')Kg', ColumnName, PATINDEX('%([ 0-9]%)Kg%', ColumnName)) -
PATINDEX('%([ 0-9]%)Kg%', ColumnName) - 1))) as ColumnName
FROM #T
WHERE ColumnName LIKE '%([ 0-9]%)Kg%'
)derived
EX:
SELECT SUBSTRING('a random amount of text ( 2.3)Kg another random amount of text',PATINDEX ('%(%','a random amount of text ( 2.3)Kg another random amount of text')+1,
PATINDEX ('%)%','a random amount of text ( 2.3)Kg another random amount of text')-PATINDEX ('%(%','a random amount of text ( 2.3)Kg another random amount of text')-1)
WHERE Clause
Open in new window