0

So say I have a table named Description with a column that store strings listing student descriptions...

St_Desc
Student: Adam / Age: 19 / Major: English
Student: Ben / Age: 22 / Major: Biology

And I have another table named Info that stores student info

St_Id | St_Name
1000  | Adam
1001  | Ben

So for the table with column St_Desc I want to replace all of the names with their respective Id number. So the result I'm looking for is this..

St_Desc
Student: 1000 / Age: 19 / Major: English
Student: 1001 / Age: 22 / Major: Biology

What I have tried so far is storing St_Name and St_Id in separate variables and then doUPDATE [dbo].[Description] SET [St_Desc] = REPLACE([St_Desc], @name, @id). But that didn't change anything. I don't know how I can get the REPLACE function to go through all the names and replace them with its ids. I'm not very familiar with sql scripting so feel free to go into detail. Thank you.

Improve this question
1

1 Answer 1

Reset to default
3

I think a simple Replace() would do the trick in concert with a JOIN

Declare @Table1 table (ST_Desc varchar(max))
Insert Into @table1 values
('Student: Adam / Age: 19 / Major: English'),
('Student: Ben / Age: 22 / Major: Biology')

Declare @Table2 table (St_Id int,St_Name varchar(50))
Insert Into @Table2 values
(1000,'Adam'),
(1001,'Ben')

Select St_Desc  = Replace(St_Desc,St_Name,St_ID)
--Update @Table1 Set St_Desc = Replace(St_Desc,St_Name,St_ID)
 From  @Table1 A
 Join  @Table2 B on CharIndex(': '+St_Name+' /',St_Desc)>0

If you are satisfied with the results un-comment the Update and remove the Select

Returns

St_Desc
Student: 1000 / Age: 19 / Major: English
Student: 1001 / Age: 22 / Major: Biology
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

@alicem_ct Looking for a specific string within a string. LIKE would also be an option, but CharIndex can have better performance than a non-sargable LIKE
Should everything else work the same if I used statements such as Insert Into @t1 select St_Desc from [dbo].[Description]; instead of typing out the values line by line? I refreshed my database but the names did not change into its id.
@alicem_ct The Declare (at)Table1/2 are just demonstrative table variables. Replace (at)Table1 and 2 with your actual table names
@alicem_ct Modified the answer to include an UPDATE. Once you are happy with the results remove the comment out the Select St_Desc line and un-comment the Update line
It worked thank you for your time! The Select statement alone doesn't change anything in my DB but using the Update statement does it. I also had errors saying some column names were ambiguous so I specified the name with Info.St_Name and Info.St_Id to fix that.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.