1

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?

  • 1
    Did you try LIKE '%Mark%' ? – ypercubeᵀᴹ Jan 6 '18 at 17:36
  • @ypercubeᵀᴹ Yes, that works. But I want to remove all my white space after my string. I also tried with RTRIM. – grrigore Jan 6 '18 at 17:37
  • 1
    So, what other white space characters have you got in there, besides the \n? – ypercubeᵀᴹ Jan 6 '18 at 17:43
  • @ypercubeᵀᴹ I also have spaces. Some of them look like the example above (with Mark) and some of them look like this "Mark " and at the end of the spaces it also has a new line. – grrigore Jan 6 '18 at 17:45
3

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
;
  • That's still not working :(. It does say that my rows are affected, but the changes are not saved. – grrigore Jan 6 '18 at 17:57
  • 1
    Did it work in the end? Can you give us a sample of the row - if it didn't? – ypercubeᵀᴹ Jan 6 '18 at 18:05
  • Well it didn't, but your answer was right for my question. I run the query, it says all my rows are affected but when I check if the changes are saved they aren't. It looks like it does nothing. I mean the update isn't really updating the rows. – grrigore Jan 6 '18 at 18:08
  • 1
    I tried in dbfiddle.uk and it works. Are you sure you had CHAR(10) there? Can you show us which UPDATE statement did you try and also show us a row that was not updated? – ypercubeᵀᴹ Jan 6 '18 at 18:16
  • 2
    I don't think REPLACE has changed through versions. Perhaps you have /r characters as well. CHAR(13) – ypercubeᵀᴹ Jan 6 '18 at 18:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.