Solved

How to return only part of a string field in sql query

Posted on 2010-09-10
14
578 Views
Last Modified: 2012-05-10
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?
0
Comment
Question by:alexking
  • 5
  • 5
  • 4
14 Comments
 
LVL 16

Expert Comment

by:vdr1620
ID: 33646512
try the below SQL

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


SELECT SUBSTRING(ColumnName,PATINDEX ('%(%',ColumnName)+1,
                    PATINDEX ('%)%',ColumnName)-PATINDEX ('%(%',ColumnName)-1)
WHERE ColumnName LIKE '%)Kg%'

Open in new window

0
 
LVL 16

Accepted Solution

by:
vdr1620 earned 250 total points
ID: 33646536
small Correction add a FROM Clause to the SQL above
SELECT SUBSTRING(ColumnName,PATINDEX ('%(%',ColumnName)+1,
           PATINDEX ('%)%',ColumnName)-PATINDEX ('%(%',ColumnName)-1)
FROM tableName
WHERE ColumnName LIKE '%)Kg%'

Open in new window

0
 

Author Comment

by:alexking
ID: 33646711
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.

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33646817
Try changing your pattern for PATINDEX to:

'%(%[0-9]%)Kg%'
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 250 total points
ID: 33646905
This seems somewhat more robust:


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%'

Open in new window

0
 

Author Comment

by:alexking
ID: 33647003
This is really really close but is still returning text up to the first "(" in the string if more than 1 is present.
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33647047
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(ColumnName, PATINDEX('%([ 0-9]%)Kg%', ColumnName) + 1,
      CHARINDEX(')Kg', ColumnName, PATINDEX('%([ 0-9]%)Kg%', ColumnName)) -
      PATINDEX('%([ 0-9]%)Kg%', ColumnName) - 1)))
FROM #T
WHERE ColumnName LIKE '%([ 0-9]%)Kg%'
0
 

Author Comment

by:alexking
ID: 33647139
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
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33647416
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%'

Open in new window

0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33647453
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
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33647455
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.
0
 

Author Closing Comment

by:alexking
ID: 33647504
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?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33647558
Glad to help, and no problem at all with the split :)
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33647638
also.. check this.. i added some SQL to mathew's Script


SELECT CASE WHEN LEN(ColumnName) > 6 THEN LTRIM(RTRIM(SUBSTRING(ColumnName,CharINDEX('(',ColumnName)+1,6)))
            ELSE ColumnName END
FROM
(
SELECT LTRIM(RTRIM(SUBSTRING(ColumnName, PATINDEX('%([ 0-9]%)Kg%', ColumnName) + 1,
      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
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql server computed columns 11 29
SQL Server 2012 r2 - Sum totals 2 22
Text file into sql server 5 21
Problem when I run a simple storeproc - help 4 14
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now