Simple Database Query
 

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


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?

Completely untested, but try
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.

hey scasey

thank u..

I tried it but it ignore it with no change

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.

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

What about using double quotes?
Or escaping the slash?

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




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




Thanks. I will try tonight

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

If I just ignore slashes all together and just replace the word 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

What did the error say?

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



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/'"

That query appears to have worked, except for the "0 rows afftected" part. :confused:
Try just
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.


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

Did you try escaping the slash:
Either double-quotes or escaping the slash should work, but again, test the replace syntax first with
maybe the problem lies elsewhere.

The replace() function works just fine to remove slashes like this:
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... ;)

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?

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.