trevor1940
asked on
SQLite: Remove new line from end of string
I've just run a SQL command something like this and discovered I have trailing white space in file.path how do I remove it?
Resulting in
This means movie.id 3837 is a duplicate of movie.id2407 but wasn't picked up because of the trailing white space not sure if '\n,CR or LF'
if possible before removing 3837 I'd like to grab the m.tmdbid,m.imdbid values and update 2407 if NULL the only thing that links the 2 is "path2/to/My Film Name.mp4"
I think there is over 500 duplicates with trailing white space every thing with a f.id >= 5216 maybe duplicate and have entries for m.tmdbid,m.imdbid and <= 5216 may or may not have m.tmdbid,m.imdbid values
Primarily after Removing the duplicate 3837 I then need to remove the trailing white space from f.path I can't do it before because it's indexed and won't allow duplication
select m.id ,m.title,m.tmdbid,m.imdbid,mlf.fileid,f.path from movie m,movielinkfile mlf,file f where m.id=mlf.movieid and mlf.fileid=f.id and f.path like '%My Film Name%' order by m.titleResulting in
--m.id ,m.title,m.tmdbid,m.imdbid,mlf.fileid,f.path
"2407","My Film Name","","","2569","Path1/to/My Film Name.mp4"
"2407","My Film Name","","","4850","Path2/to/My Film Name.mp4
" -- trailing white space
"3837","My Film Name","1234","tt123456","5318","Path2/to/My Film Name.mp4"This means movie.id 3837 is a duplicate of movie.id2407 but wasn't picked up because of the trailing white space not sure if '\n,CR or LF'
if possible before removing 3837 I'd like to grab the m.tmdbid,m.imdbid values and update 2407 if NULL the only thing that links the 2 is "path2/to/My Film Name.mp4"
I think there is over 500 duplicates with trailing white space every thing with a f.id >= 5216 maybe duplicate and have entries for m.tmdbid,m.imdbid and <= 5216 may or may not have m.tmdbid,m.imdbid values
Primarily after Removing the duplicate 3837 I then need to remove the trailing white space from f.path I can't do it before because it's indexed and won't allow duplication
ASKER
@Kevin Do you mean like this?
Table = file and column = path
Table = file and column = path
UPDATE file SET path = REPLACE( REPLACE( path, CHAR(13), ''), CHAR(10), '')
Yes, you could update it in the table, so you do not have to worry about it on future SELECT statements. Otherwise, you could just do the replace on the way out. I recommend before running the update, you double check that it is doing what you want.
I put the 'A' at the end , so we can see if the replace removed the characters as you otherwise probably wouldn't see the difference in command line.
Once it looks correct, yes, you can run update to fix path in the table permanently.
SELECT path || 'A', REPLACE( REPLACE( path, CHAR(13), ''), CHAR(10), '') || 'A'
FROM fileI put the 'A' at the end , so we can see if the replace removed the characters as you otherwise probably wouldn't see the difference in command line.
Once it looks correct, yes, you can run update to fix path in the table permanently.
ASKER
Just to test These don't find anything
select id,path from file where path like '%CHAR(13)%'
select id,path from file where path like '%CHAR(10)%'
select id,path from file where path like '%CHAR(13)CHAR(10)'
You cannot have the CHAR(13) inside of the string. You would have to do like this:
select id,path from file where path like '%' || CHAR(13) || '%'
select id,path from file where path like '%' || CHAR(10) || '%'
select id,path from file where path like '%' || CHAR(13) || CHAR(10) || '%'ASKER
OK This
Found every row so not sure how helpfull that is
Founbd 504 the other two found 0
SELECT path || 'A', REPLACE( REPLACE( path, CHAR(13), ''), CHAR(10), '') || 'A'
FROM fileFound every row so not sure how helpfull that is
select id,path from file where path like '%' || CHAR(10) || '%'Founbd 504 the other two found 0
ASKER
I'm guessing I should use
UPDATE file SET path = REPLACE( path, CHAR(10), '')
Maybe the characters at the end are not carriage return and line feed then. Note: the first code should return all 504 rows because we didn't put a WHERE clause. What you would be looking for on that one is any code that shows A at the end with space on the left and without on the right... it just was a technique to see the difference in a query where the characters just appear as whitespace. Another way to do it would be to check if the scrubbed string does not equal the original.
Added RTRIM to remove spaces as well.
SELECT id
FROM file
WHERE path <> RTRIM( REPLACE( REPLACE( path, CHAR(13), ''), CHAR(10), '') )Added RTRIM to remove spaces as well.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanx for your help
https://www.sqlite.org/lang_corefunc.html#replace
https://www.sqlite.org/lang_corefunc.html#char
The CHAR function will help. CHAR(13) is carriage return and CHAR(10) is a line feed. Usually they are there together. Therefore, you can do two replace strings just in case like REPLACE( REPLACE( str, CHAR(13), ''), CHAR(10), ''). I hope that makes sense.