November 3, 2015 at 12:21 pm
I am trying to find a solution to get the result set to fetch a particular string format from a table in my database, which has a column of NVARCHAR data type
CREATE TABLE #ActivityComments(Comments NVARCHAR(MAX))
INSERT INTO #ActivityComments VALUES('This is the study code for Field Phase S14-04932-01')
INSERT INTO #ActivityComments VALUES('Phase reporting has the study S15-04931-01 which is obselete')
INSERT INTO #ActivityComments VALUES('Phase running study code S14-04932-02 is not valid')
The output of the query should be like:
S14-04932-01
S15-04931-01
S14-04932-02
Is there any way possible to achieve this.. Thanks in advance.
November 3, 2015 at 12:46 pm
You can use PATINDEX and SUBSTRING do something like this.
The trick would just be to make sure you've identified a pattern or set of patterns that captures all instances of the desired strings. In this case, I'm assuming all are 'S', followed by two numbers, a hyphen, five numbers, a hyphen, and two more numbers. That at least holds for this small sample set.
For that, something like this would work:
SELECT SUBSTRING(Comments,PATINDEX('%S[0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]%',Comments),12)
FROM #ActivityComments
WHERE PATINDEX('%S[0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]%',Comments)>0
Cheers!
November 3, 2015 at 1:15 pm
Further on Jacob's excellent suggestion, check for the existence of the pattern before the substring, as you could have false positives when the pattern is not found.
😎
November 3, 2015 at 1:26 pm
Eirikur Eiriksson (11/3/2015)
Further on Jacob's excellent suggestion, check for the existence of the pattern before the substring, as you could have false positives when the pattern is not found.😎
Thanks, good catch :blush:. I've updated the query in my original post to filter out rows that lack a match to the pattern. If you want to return all rows whether they match or not, and show the matching text when there's a match and some other value when there is no match, then you could use CASE for that.
Cheers!
November 4, 2015 at 12:32 am
I am getting an error..
Please guide me on this.
SELECT
CASE WHEN PATINDEX('%S[0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]%',Comments)>0
THEN
SUBSTRING(Comments,PATINDEX('%S[0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]%',Comments),12)
FROM #ActivityComments
ELSE
SELECT ' '
END AS ActivityComments
November 4, 2015 at 12:38 am
Quick fix
😎
CREATE TABLE #ActivityComments(Comments NVARCHAR(MAX))
INSERT INTO #ActivityComments VALUES('This is the study code for Field Phase S14-04932-01')
INSERT INTO #ActivityComments VALUES('Phase reporting has the study S15-04931-01 which is obselete')
INSERT INTO #ActivityComments VALUES('Phase running study code S14-04932-02 is not valid')
SELECT
CASE
WHEN PATINDEX('%S[0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]%',Comments)>0 THEN
SUBSTRING(Comments,PATINDEX('%S[0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]%',Comments),12)
ELSE ' '
END AS ActivityComments
FROM #ActivityComments
DROP TABLE #ActivityComments;
November 4, 2015 at 12:55 am
Thank You Eirikur.. Loved it..
One more help. Is it feasible for this value to be shown
INSERT INTO #ActivityComments VALUES('S14-04932-01 + S15-04931-01 + S14-04932-02')
Output
----------
S14-04932-01, S15-04931-01, S14-04932-02'
November 4, 2015 at 1:03 am
Junglee_George (11/4/2015)
Thank You Eirikur.. Loved it..One more help. Is it feasible for this value to be shown
INSERT INTO #ActivityComments VALUES('S14-04932-01 + S15-04931-01 + S14-04932-02')
Output
----------
S14-04932-01, S15-04931-01, S14-04932-02'
That's an entirely different ball game, most commonly this would be done by either splitting the string or recursively iterate through it, the latter normally slower. Suggest you post this problem as a new question on the forum.
😎
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply