Extract specific words from row or leave blank?

Question

Extract specific words from row or leave blank?

sneekyguy

Newbie

Points: 7
More actions
February 26, 2016 at 8:17 am

#308422

Hi, I am trying to do something pretty simple on the face of it using t-sql; extracting two words say 'of data' from a row if it has it in it, or leave blank?
Anyone have a suggestion for an easy way of doing this..

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

amos-870870 SSC Veteran Points: 254 More actions · Answer 2

This is a simple example, and could be used if you know the phrase you are looking for. You could expand on it to be more dynamic. Others can chime in on the efficiency of the code.

DECLARE@KEY_PHRASE VARCHAR(255)

SET @KEY_PHRASE = 'of data'

SELECT [col1]

,[col2]

, CASE WHEN [col2] LIKE '%' + @KEY_PHRASE + '%' THEN 'of data' ELSE NULL END AS Result

FROM[Table_1]

col1col2Result

1This row contains the phrase of data and also some other wordsof data

2This row does not contain the key phraseNULL

Alan Burstein SSC Guru Points: 61152 More actions · Answer 3

Building on Amos' solution... Note that you don't need "ELSE NULL" in a CASE statement. Returning NULL is the default behavior when an ELSE is not specified.

DECLARE @table1 TABLE (col1 int, col2 varchar(100));

INSERT @table1 VALUES

(1,'This row contains the phrase of data and also some other wordsof data'),

(2,'This row does not contain the key phrase');

DECLARE@KEY_PHRASE VARCHAR(255) = 'of data';

SELECT col1,col2,

CASE WHEN [col2] LIKE '%' + @KEY_PHRASE + '%' THEN 'of data' END AS Result

FROM @table1;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001