how to split entry from column?

[techker]

hey guys i have a table streams

that has multiple columns but i need one and brake the string?

stream_source

contains

["http:\/\/server:8080\/9Lx0YfoxZp\/DexfrUxcn\/2107"]

 

so i need just : http:\/\/server:8080 as dns   9Lx0YfoxZp as username  and DexfrUxcn as password

i have tried a few was like this example..

 

but no luck im guessing its because i first need to remove ["  ?

SELECT SUBSTRING_INDEX(stream_source, '  \/ ', 1) AS dns, SUBSTRING_INDEX(SUBSTRING_INDEX(stream_source,'  \/ ', 2), ' ',-1) AS mname, SUBSTRING_INDEX(stream_source, ' \/ ', -1) as lname FROM streams;

[requinix]

Why are you storing JSON in there? Can you switch to a JSON data type? Does it have to be SQL to get the information, given that code could probably do it more easily?

[techker]

the database entry is json format

 

["http:\/\/server:8080\/9Lx0YfoxZp\/DexfrUxcn\/2107"]  and has other fields like name......

cause the panel fetched the info and encodes it in json for output to selected file needed...

but i just noticed that it has all entries so database table can contain 15000 streams...

so i would need to check and remove duplicates....

or like you say i can use the existing api and read the json but it can take long time if to many results....

[requinix]

Switch to a JSON data type for that column, deal with any invalid data that crept in, then create a VIEW that does the nasty work of extracting values and parsing strings and whatever.

Then use the VIEW for your deduplication work.

[techker]

ya i think thats what il do ..lol thx