SQL: Replace ID in String

Question

Here's the scenario --

I have two tables Names & Sentences:

Names                   Sentences
ID | Names              ID | Description                   
1  | Fox                1  | The <1> jumped over the <2>
2  | Narwhal            2  | The <2> sailed to the <3>
3  | Moon

I need to return all the Descriptions from the Sentences table with the ID replaced with the corresponding Name from the Names table, e.g.

'The Fox jumped over the Narwhal', 'The Narwhal sailed to the Moon'.

I'm stumped at where to begin with this, in theory it appears like a simple problem but my knowledge of manipulating Strings is limited.

Any help would be much appreciated!

Thanks.

Btw, this doesn't sound like a problem you can handle with SQL Queries. It would be better to handle it in code. — everag
– everag, Commented Jan 24, 2012 at 20:26
I have no idea how to solve that in a dynamic way with ANSI SQL, but take a look at the REPLACE command, might help you. — Ortwin Angermeier
– Ortwin Angermeier, Commented Jan 24, 2012 at 20:35
I was hoping that there might be a solution such as - SELECT REPLACE(SENTENCES.DESCRIPTION, [SQL query to identify id/s in <>], NAMES.NAMES). Or to that effect. EDIT Ortang got in just before me, thanks I'll have to investigate whether it's possible. — ephron
– ephron, Commented Jan 24, 2012 at 20:36
@ephron - It's doable but not pretty. If there was only ONE set of replaces it would be pretty straightforward. See my answer below. — JNK
– JNK, Commented Jan 24, 2012 at 20:37

JNK · Accepted Answer · 2012-01-24 20:35:19Z

1

This should at least get you started. This is messy to do because you have multiple-level replacements, and no JOIN key in the data itself.

This uses two CROSS JOINs and a bunch of string functions, so it will be very inefficient. This will never run quickly on a large data set.

DECLARE @names table (id int, names varchar(100))
DECLARE @sent table (id int, descr varchar(1000))

INSERT INTO @names
VALUES
(1, 'Fox'),
(2, 'Narwhal'),
(3, 'Moon')

INSERT INTO @sent 
VALUES
(1, 'The <1> jumped over the <2>.'),
(2, 'The <2> sailed to the <3>.')

SELECT DISTINCT Filtered
FROM (SELECT Replace(REPLACE(descr, '<' + CAST(n.id as varchar) + '>', n.names), '<' + CAST(n2.id as varchar) + '>', n2.names) Filtered
      FROM @sent s, @names n, @names n2) x
WHERE Filtered NOT LIKE '%<%'

answered Jan 24, 2012 at 20:35

JNK

65.5k15 gold badges127 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ephron Over a year ago

Thanks JNK, fortunately it's for a one-off use at work and isn't going to be executed on a large set of data.

Community · Accepted Answer · 2017-05-23 12:20:01Z

0

You could write a function and then select using that function. But, as JNK said, I'm not sure how to do it with two replaces. My question and reply (SQL Server change font in html string) gives an example of replacing one string with another using a function. You're essentially replacing '<1>' with a selection set and then '<2>' with a selection set for each of the first set.

edited May 23, 2017 at 12:20

CommunityBot

11 silver badge

answered Jan 24, 2012 at 20:50

thursdaysgeek

7,99621 gold badges82 silver badges119 bronze badges

Comments

Andrew · Accepted Answer · 2012-01-24 20:59:42Z

0

Another approach that might be faster at the expense of burning yr retinas:

SELECT
LEFT(Description, CHARINDEX('<', Description)-1)
+ Name1
+ SUBSTRING(Description, CHARINDEX('>', Description) + 1, CHARINDEX('<', Description, CHARINDEX('<', Description) + 1) - CHARINDEX('>', Description) - 1)
+ Name2
FROM Sentences
JOIN Names AS Name1 ON Name1.ID = CAST(SUBSTRING(Description, CHARINDEX('<', Description) + 1, 1) AS INT)
JOIN Names AS Name2 ON Name2.ID = CAST(SUBSTRING(Description, CHARINDEX('<', Description, CHARINDEX('<', Description) + 1) + 1, 1) AS INT)

answered Jan 24, 2012 at 20:59

Andrew

4,62429 silver badges31 bronze badges

Collectives™ on Stack Overflow

SQL: Replace ID in String

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related