Simple Database Query

xray55 · 10-27-2019, 12:21 AM

MySql57
table name: streams
field name: stream_source

Hey guys,

Im trying to edit about 235 weblinks inside a small database.

I want to remove the highlighted characters

http://domain:9595/live/USERNAME/PASSWORD/425.m3u8

and end up with streams that look like

http://domain:9595/USERNAME/PASSWORD/425

removing find/replace .m3u is easy enough but I need to also remove the word live and one of the slashes besides it so I dont end up with double slashes //

This is what I attempted

Code:

update streams set stream_source = replace(stream_source, '/live/', '/');
update streams set stream_source = replace(stream_source, '.m3u8', '');
exit;

the .m3u8 is replaced with blank so that works but slash / is not replacing /live/

my understanding is that u cant search and replace slashes /

what can I do to get around this?

scasey · 10-27-2019, 01:43 AM

Completely untested, but try

Code:

update streams set stream_source = replace(stream_source, 'live/', '');

Also, try your code and my try using double quotes.
And try escaping the slash.
[Confession: I did not know about the replace() function until now...very cool -- I don't have time to look it up right now...getting late here. ]

Although I will point out that a double slash is treated the same as a single slash, usually.

Code:

[root@localhost:~]# ls -l lqtest/testdir/
total 0
-rw-r--r-- 1 root root 0 Oct 26 23:42 file1
-rw-r--r-- 1 root root 0 Oct 26 23:42 file2
[root@localhost:~]# ls -l lqtest//testdir/
total 0
-rw-r--r-- 1 root root 0 Oct 26 23:42 file1
-rw-r--r-- 1 root root 0 Oct 26 23:42 file2

xray55 · 10-27-2019, 03:10 AM

hey scasey

thank u..

I tried it but it ignore it with no change

Code:

mysql> update streams set stream_source = replace(stream_source, 'live/', '');
Query OK, 0 rows affected (0.02 sec)
Rows matched: 1251  Changed: 0  Warnings: 0

berndbausch · 10-27-2019, 03:19 AM

You could move the problem to bash level. I.e. write a shell script that uses mysql with the -e option to first obtain the URL, then processes it with the usual suspects sed and/or awk, finally update the record again with mysql -e.

AnanthaP · 10-27-2019, 04:06 AM

The right most 3 bytes seems easy enough. replace(field_name) with left(FIELD_NAME, len(FIELD_NAME),3) ..

Next, to remove the word live that is obtained as the second element when the contents are SPLIT with forward slash as the delimiter. I am looking at using SPLIT with SUBSTRING_INDEX. I just googled and got this. https://coderwall.com/p/zzgo-w/split...ngs-with-mysql Does it help? Maybe SUBSTRING_INDEX - I didn't know about - can be used for the first part (last 3 bytes) also.

Alternatively, if it a small table (less rows) AND you can identify a unique field AND it's a one time job,
1. Dump out to a text file,
2. Change,
3. Update back.

OK

scasey · 10-27-2019, 04:19 AM

Quote:

Originally Posted by xray55

hey scasey

thank u..

I tried it but it ignore it with no change

Code:

mysql> update streams set stream_source = replace(stream_source, 'live/', '');
Query OK, 0 rows affected (0.02 sec)
Rows matched: 1251  Changed: 0  Warnings: 0

What about using double quotes?

Code:

mysql> update streams set stream_source = replace(stream_source, "live/", '');

Or escaping the slash?

Code:

mysql> update streams set stream_source = replace(stream_source, 'live\/', '');

xray55 · 10-27-2019, 01:56 PM

Quote:

Originally Posted by berndbausch

You could move the problem to bash level. I.e. write a shell script that uses mysql with the -e option to first obtain the URL, then processes it with the usual suspects sed and/or awk, finally update the record again with mysql -e.

thanks berndbausch, I can barely write simple scripts. I dont think I can handle that one lol.

Quote:

Originally Posted by AnanthaP

The right most 3 bytes seems easy enough. replace(field_name) with left(FIELD_NAME, len(FIELD_NAME),3) ..

Next, to remove the word live that is obtained as the second element when the contents are SPLIT with forward slash as the delimiter. I am looking at using SPLIT with SUBSTRING_INDEX. I just googled and got this. https://coderwall.com/p/zzgo-w/split...ngs-with-mysql Does it help? Maybe SUBSTRING_INDEX - I didn't know about - can be used for the first part (last 3 bytes) also.

Alternatively, if it a small table (less rows) AND you can identify a unique field AND it's a one time job,
1. Dump out to a text file,
2. Change,
3. Update back.

OK

Thanks AnanthaP, I knew that was an option. I was afraid something would get changed or screwed up when I resave.

Quote:

Originally Posted by scasey

What about using double quotes?

Code:

mysql> update streams set stream_source = replace(stream_source, "live/", '');

Or escaping the slash?

Code:

mysql> update streams set stream_source = replace(stream_source, 'live\/', '');

Thanks. I will try tonight

xray55 · 10-27-2019, 02:23 PM

scasey, unfortunately, they didnt work, both caused sql error in your SQL syntax.

xray55 · 10-27-2019, 02:27 PM

If I just ignore slashes all together and just replace the word live

Code:

update streams set stream_source = replace(stream_source, 'live', '');

It will end up with a link

http://domain:9595//USERNAME/PASSWORD/425

which isnt the end of the world, double slashed links // will still resolve. At least in a browser.

I just wanted a clean replace and to finally learn how to replace special characters with sql

scasey · 10-27-2019, 03:08 PM

Quote:

Originally Posted by xray55

scasey, unfortunately, they didnt work, both caused sql error in your SQL syntax.

What did the error say?

xray55 · 10-27-2019, 05:48 PM

Quote:

Originally Posted by scasey

What did the error say?

Code:

update streams set stream_source = replace(stream_source, 'live/', '');

mysql> update streams set stream_source = replace(stream_source, 'live/', '');
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1272 Changed: 0 Warnings: 0

Code:

mysql> update streams set stream_source = replace(stream_source, "live/", '');

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql > update streams set stream_source = replace(stream_source, "live/", '')' at lin e 1

Should I use single quotes around the double quoutes or vice versa?

"live/"
'"live/"'
"'live/'"

scasey · 10-27-2019, 07:27 PM

Quote:

Originally Posted by xray55

Code:

update streams set stream_source = replace(stream_source, 'live/', '');

mysql> update streams set stream_source = replace(stream_source, 'live/', '');
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1272 Changed: 0 Warnings: 0

That query appears to have worked, except for the "0 rows afftected" part.

Try just

Code:

SELECT replace(stream_source, 'live/', '') FROM stream where stream_source like '%live%';

to test. It won't change anything in the database, just dump the output to the screen. A slash is not a special character, as far as I know. It shouldn't matter. Testing the replace() syntax without trying to change the database is where to start. IMO.

Quote:

Originally Posted by xray55

Code:

mysql> update streams set stream_source = replace(stream_source, "live/", '');

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql> update streams set stream_source = replace(stream_source, "live/", '')' at line 1

That attempt appears to have included the mysql prompt (highlighted).

Did you try escaping the slash:

Code:

update streams set stream_source = replace(stream_source, 'live\/', '')

Quote:

Originally Posted by xray55

Should I use single quotes around the double quoutes or vice versa?

"live/"
'"live/"'
"'live/'"

Either double-quotes or escaping the slash should work, but again, test the replace syntax first with

Code:

SELECT replace(...) from streams where ...;

maybe the problem lies elsewhere.

scasey · 10-27-2019, 07:40 PM

The replace() function works just fine to remove slashes like this:

Code:

SELECT caption, replace(caption, '/', '') FROM `photos` WHERE caption like '%/%'

Of course, you don't want to remove all the slashes (nor did I in that example...I'd have messed up several records in my database). Once I get that query refined, I'll try your UPDATE SET.

Definitely want to pay attention to the WHERE clause...

Code:

SELECT caption, replace(caption, '/', '') FROM `photos` WHERE caption like '%in/%'
Modeling in/ New York for Barbizon -- 1943 	Modeling in New York for Barbizon -- 1943

So the problem is not the slash...

UPDATE: Syntax using your UPDATE...SET logic worked just fine. That caption had the / removed from the only row that contained an '%in/%. The slash is not the problem with your syntax.

Do you (the user you're logged into mysql with) have UPDATE privileges on the table?

xray55 · 10-27-2019, 08:50 PM

Quote:

Originally Posted by scasey

The replace() function works just fine to remove slashes like this:

Code:

SELECT caption, replace(caption, '/', '') FROM `photos` WHERE caption like '%/%'

UPDATE: Syntax using your UPDATE...SET logic worked just fine. That caption had the / removed from the only row that contained an '%in/%. The slash is not the problem with your syntax.

Do you (the user you're logged into mysql with) have UPDATE privileges on the table?

Yes, its root user

scasey · 10-27-2019, 09:21 PM

Quote:

Originally Posted by xray55

Yes, its root user

You have a MySQL user ‘root’?
Although in your OP you said that replacing the ‘. m3u8’ worked...so it must not be permissions...it was just a thought — an “is it plugged in?” question. Sorry.

Did you test what you’re trying with the SELECT…replace(…) syntax? What happened?

Again, the slash made no difference in my tests. There’s nothing special about a slash in that context.