Simple Database Query

xray55 · 10-29-2019, 09:59 PM

Quote:

Originally Posted by scasey

Oh!
The data you're showing us now still has the .m3u8, in your OP you posted that the replace of the .m3u8 had worked.
Why is it still there?

I restored the db to leave it untouched. Was just saying it did work for update replace .m3u8

Code:

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

The output is messy in terminal windows but lined up better in ultraedit

Code:

| ["http:\/\/domain:25461\/live\/JayStevens\/kjafijq25251\/2056.m3u8"]                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | ["http:\/\/domain:25461\/live\/JayStevens\/kjafijq25251\/2056.m3u8"]                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| ["http:\/\/domain:25461\/live\/JayStevens\/kjafijq25251\/2057.m3u8"]                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | ["http:\/\/domain:25461\/live\/JayStevens\/kjafijq25251\/2057.m3u8"]                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| ["http:\/\/domain:25461\/live\/JayStevens\/kjafijq25251\/2058.m3u8"]                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | ["http:\/\/domain:25461\/live\/JayStevens\/kjafijq25251\/2058.m3u8"]                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
301 rows in set (0.00 sec)

scasey · 10-30-2019, 12:11 AM

Hmmmm.
Post without the long line of ----- perhaps.

Not sure why the output would be messy on the terminal...does it wrap? Why? Is there a longer entry you're not showing us? On my terminal, the space between the pipes appears to be set to the length of the longest entry.

Anyway,
So that didn't work. Is it possible that the data is not what it appears? Maybe the \ or the / are not what they appear to be...not sure how we'd check that. Is there a way to see the string in hexadecimal, I wonder?

Very strange.
I just tried it again, and it works as expected for me. The replace() sees and replaces \/ just fine.
Going to have to tinker with it a bit more.

Try this:

Code:

SELECT stream_source, replace(stream_source, 'live\/', '') FROM streams WHERE stream_source like '%live\/%';

to see if the \/ is a match...

xray55 · 10-30-2019, 12:30 AM

Quote:

Originally Posted by scasey

Hmmmm.
Post without the long line of ----- perhaps.

Not sure why the output would be messy on the terminal...does it wrap? Why? Is there a longer entry you're not showing us? On my terminal, the space between the pipes appears to be set to the length of the longest entry.

No, doesnt wrap

Quote:

Originally Posted by scasey

Hmmmm.
Try this:

Code:

SELECT stream_source, replace(stream_source, 'live\/', '') FROM streams WHERE stream_source like '%live\/%';

to see if the \/ is a match...

Code:

mysql> SELECT stream_source, replace(stream_source, 'live\/', '') FROM streams WHERE stream_source like '%live\/%';
Empty set (0.00 sec)

The entire DB is only 25mb

I manually browsed a dump with Ultraedit and found double escapes

Code:

"http:\\/\\/domain.com\\/live\\/JayStevens\\/kjafijq25251\\/301.m3u8\"

xray55 · 10-30-2019, 12:35 AM

which then suggested

Code:

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

or

Code:

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

or

Code:

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

and bingo!

Code:

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

Code:

mysql> update streams set stream_source = replace(stream_source, '.m3u8', '');
Query OK, 214 rows affected (0.02 sec)
Rows matched: 1317  Changed: 214  Warnings: 0

scasey · 10-30-2019, 01:08 AM

TADA!!

Good job xray55. That was a good piece of detective work.
You can mark this thread SOLVED using the Thread Tools at the top of any page.

Hopefully our discussion will help others in the future. It was a strange situation/condition. Makes me wonder where those entries came from. It shouldn't be necessary to escape slashes when writing URLs to a database. It most certainly shouldn't be necessary to store the escape characters.

I do need to escape single quotes when writing names, so that the apostrophe in O'Casey doesn't mess up the insert statement...but the \ doesn't get stored in the database when I do that.

Doesn't matter. It is what it is. Now you know what the data really looks like, and you were able to make the changes you wanted, so task completed.

I was happy to help.

xray55 · 10-30-2019, 01:15 AM

Thanks pal for not giving up on me.

Like i said, I had easy solutions. I could have easily mass deleted from GUI and reuploaded a corrected list but i wanted to once and for all understand why in this type of DB I can never use the replace command on certain characters.

So to finalize my understanding of escapes? They wrap to ignore a special DB function of that character its wrapping?

scasey · 10-30-2019, 09:32 AM

Quote:

Originally Posted by xray55

Thanks pal for not giving up on me.

Like i said, I had easy solutions. I could have easily mass deleted from GUI and reuploaded a corrected list but i wanted to once and for all understand why in this type of DB I can never use the replace command on certain characters.

So to finalize my understanding of escapes? They wrap to ignore a special DB function of that character its wrapping?

Not sure I understand your question. I can think of no reason to have escapes within the data as you do.
What complicated this was that the data contained an escaped backslash (\\), which rendered as a single backslash when queried, so you couldn't see what to match. So, again, it's not about "certain characters," it is because you didn't know what to match. Once you did, the replace function worked just fine, yes?

The only time I use an escape is when the VALUE of a column delimited by single quotes contains an apostrophe:

Code:

UPDATE table SET name='O\'Hara'

and in that case the backslash does not get written into the data. I do that when I'm creating the SQL statement programmatically. If I were typing it, I could just use double quotes:

Code:

UPDATE table SET name="O'Hara"