Update Text Field on Multiple Rows Using Current Text In Field SQL

user_9852587 0
Viewed 121

I have a table with a text field that imported with incorrect characters delimited by a space.

---------------------
myTable:

id     text

1      text BADCHARACTERS
2      text2 DIFFERENTBADCHARACTERS
3      text3 ASDF
4      text4 VERYBADCHARACTERS
---------------------

Expected Output

---------------------
myTable:

id     text

1      text
2      text2
3      text3
4      text4
---------------------

I need to run an update to remove all the text AFTER the space on a specific number of rows. I figured out a way to select the correct text, but I'm stuck on how to update in bulk.

SELECT LEFT(text, CHARINDEX(' ', text) -1)
FROM myTable
WHERE ID = 1

Will return the correct string.

Any thoughts on how to update a specific set records with the respective text in each row?

import ssms sql-server sql string
2 Answers

You can put the logic in your select directly into an update:

update mytable
    set text = LEFT(text, CHARINDEX(' ', text) -1)
    where ID = 1 and text like '% %';
user_11440350

I figured out a solution, though I'm not sure how optimized it is. I use a while loop with a counter starting at the first ID and ending at the last. Luckily the records I needed updating were in order. Here is my solution:

DECLARE @Counter INT = {First ID}
WHILE (@Counter <= {Last ID})
BEGIN
    UPDATE myTable
    SET text = (SELECT LEFT(text, CHARINDEX(' ', text) -1)
                FROM myTable
                WHERE ID = @Counter)
    WHERE id = @Counter
    SET @Counter = @Counter + 1
END
user_98525870
Related