Parse string based on value in another table

Question

Post reply

Parse string based on value in another table

LASilvey

SSC Enthusiast

Points: 145
More actions
January 5, 2009 at 6:29 pm

#75392

Hello!
I have two tables; StringTable and ExcludedStringTable. One table contains strings to be evaulated; the other contains string values that must be removed from said string. The "new" value (missing the excluded values) will update into a separate column in my StringTable.
Example:
StringTable:
'Hello World' -----> updates to 'World'
'I said hello' -----> updates to 'said hello'
ExcludedStringTable:
'Hello'
'I'
Does this make sense? I'm not really sure where to start with this one, or how to cycle through all the values in each table.
Thanks so much !

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

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

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 1

And, you want it to be case sensitive?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LASilvey SSC Enthusiast Points: 145 More actions · Answer 2

LASilvey

SSC Enthusiast

Points: 145

January 6, 2009 at 7:08 am

#921753

At this point, that is not a requirement. Thanks !

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

I'm assuming that the two tables are linked by some common column, as you have not provided enough information, here is the query which will does exactly what you have requested for.

UPDATE ST

SET ST.StringText = REPLACE( ST.StringText, EST.ExcludedStringText, '' )

FROM dbo.StringTable ST

INNER JOIN dbo.ExcludedStringTable EST ON ST.SomeCommonColumn = EST.SomeCommonColumn

--Ramesh

LASilvey SSC Enthusiast Points: 145 More actions · Answer 4

At this point, the two tables are not related, outside of the fact that these two values need to be compared. What would your recommendation be?

Garadin One Orange Chip Points: 29613 More actions · Answer 5

Here's a method. Ordinarily I wouldn't suggest this, but it sounds to me like it may be a one time thing for cleanup, and this is a fairly easy way to do it. If this is going to be an ongoing project, someone else may be able to suggest a more efficient method of pulling this off. Note that there are some very severe things to watch with your excluded words, which you will notice if you run this script. Pieces of a word like replacing the 'an' within the word 'and' are a major concern with this method. You can get around those issues somewhat by making you replace word ' an ' instead of 'an', but then you need to make sure you're comparing your original string with padded spaces, or it won't work for the first / last words in a string.

In any case, here's a cursor method. (Bah at having to write one of those again!)

[font="Courier New"]CREATE TABLE A(

Orig VARCHAR(100))

CREATE TABLE B(

Bad VARCHAR(25))

INSERT INTO A(Orig)

SELECT 'This is a string with A E I O U' UNION ALL

SELECT 'This''sin has an A and an E' UNION ALL

SELECT 'This one only has an I'

INSERT INTO B(Bad)

SELECT 'is' UNION ALL

SELECT 'with' UNION ALL

SELECT 'only' UNION ALL

SELECT 'one'

DECLARE @SQL VARCHAR(1000),

@Bad VARCHAR(25)

DECLARE ArghCursor CURSOR FOR

SELECT Bad FROM B

OPEN ArghCursor

FETCH NEXT FROM ArghCursor INTO @Bad

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQL = 'UPDATE A ' +

'SET Orig = REPLACE(Orig,''' + @Bad +''','''')'

--PRINT(@SQL)

EXEC(@SQL)

FETCH NEXT FROM ArghCursor INTO @Bad

END

CLOSE ArghCursor

DEALLOCATE ArghCursor

SELECT * FROM A

DROP TABLE A

DROP TABLE B

--------- RESULTS -----------

-- Th a string A E I O U

-- Th'sin has an A and an E

-- Th has an I

[/font]

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Garadin One Orange Chip Points: 29613 More actions · Answer 6

Here's an example of what I was talking about with the space padding.

[font="Courier New"]CREATE TABLE A(

Orig VARCHAR(100))

CREATE TABLE B(

Bad VARCHAR(25))

INSERT INTO A(Orig)

SELECT 'This is a string with A E I O U' UNION ALL

SELECT 'This''sin has an A and an E' UNION ALL

SELECT 'This one only has an I'

INSERT INTO B(Bad)

SELECT ' is ' UNION ALL

SELECT ' with ' UNION ALL

SELECT ' only ' UNION ALL

SELECT ' one '

DECLARE @SQL VARCHAR(1000),

@Bad VARCHAR(25)

DECLARE ArghCursor CURSOR FOR

SELECT Bad FROM B

OPEN ArghCursor

FETCH NEXT FROM ArghCursor INTO @Bad

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQL = 'UPDATE A ' +

'SET Orig = LTRIM(RTRIM(REPLACE('' '' + Orig + '' '',''' + @Bad +''','' '')))'

--PRINT(@SQL)

EXEC(@SQL)

FETCH NEXT FROM ArghCursor INTO @Bad

END

CLOSE ArghCursor

DEALLOCATE ArghCursor

SELECT * FROM A

DROP TABLE A

DROP TABLE B

--------- RESULTS -----------

-- This a string A E I O U

-- This'sin has an A and an E

-- This has an I

[/font]

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]