SQLite – Replace and Update All Rows

sqlite

I have a table called Players and columns Name, PlayerID. I am using sqlite under DB Browser for SQLite.

Unfortunately, all my player's names have a something like a "\n" (turned out to be a /r) at the end of the name.

Ex:

"Mark
"

I tried to Update & Replace all the names with the following query (I have like 450 rows in the table):

UPDATE Players
SET Name = REPLACE(Name,CHAR(10),'')
WHERE PlayerID <= 500

When I execute something like:

SELECT * FROM Players
WHERE Players.Name LIKE 'Mark'

it'll return no rows because of the end line.

I want to change all my rows from this format

"Mark
"

to this

"Mark"

and save all the changes.

How can I solve my problem? What's wrong?

Best Answer

If you want to remove all line feed characters (\n), then what you have is ok. You could also remove the WHERE, to update all rows:

UPDATE Players
SET Name = REPLACE(Name, CHAR(10), '')
-- WHERE PlayerID <= 500
;

Similarly, if you want to remove all spaces, do the same:

UPDATE Players
SET Name = REPLACE(Name, ' ', '')
-- WHERE PlayerID <= 500
;

Beware though that the above will change 'Mark Jenkins' to 'MarkJenkins'. Are you sure you want to do that?

You could also do both actions in a single statement, with:

SET Name = REPLACE(REPLACE(Name, CHAR(10), ''), ' ', '')

Before running the actual update, you could examine the changes with:

SELECT Name,
       REPLACE(Name, CHAR(10), '') AS NewName_NoLinefeed,
       REPLACE(Name, ' ', '') AS NewName_NoSpace,
       REPLACE(REPLACE(Name, CHAR(10), ''), ' ', '')
           AS NewName_NoLinefeed_NoSpace
FROM Players
-- WHERE PlayerID <= 500
;
Related Question