SQL SELECT everything after a certain character

Question

I need to extract everything after the last '=' (http://www.domain.com?query=blablabla - > blablabla) but this query returns the entire strings. Where did I go wrong in here:

SELECT RIGHT(supplier_reference, CHAR_LENGTH(supplier_reference) - SUBSTRING('=', supplier_reference)) 
FROM ps_product

Answer 1

Try this in MySQL.

right(field,((CHAR_LENGTH(field))-(InStr(field,','))))

Answer 2

select SUBSTRING_INDEX(supplier_reference,'=',-1) from ps_product;

Please use http://www.w3resource.com/mysql/string-functions/mysql-substring_index-function.php for further reference.

Answer 3

I've been working on something similar and after a few tries and fails came up with this:

Example: STRING-TO-TEST-ON = 'ab,cd,ef,gh'

I wanted to extract everything after the last occurrence of "," (comma) from the string... resulting in "gh".

My query is:

SELECT SUBSTR('ab,cd,ef,gh' FROM (LENGTH('ab,cd,ef,gh') - (LOCATE(",",REVERSE('ab,cd,ef,gh'))-1)+1)) AS `wantedString`

Now let me try and explain what I did ...

1. I had to find the position of the last "," from the string and to calculate the wantedString length, using LOCATE(",",REVERSE('ab,cd,ef,gh'))-1 by reversing the initial string I actually had to find the first occurrence of the "," in the string ... which wasn't hard to do ... and then -1 to actually find the string length without the ",".

2. calculate the position of my wantedString by subtracting the string length I've calculated at 1st step from the initial string length:

   LENGTH('ab,cd,ef,gh') - (LOCATE(",",REVERSE('ab,cd,ef,gh'))-1)+1

I have (+1) because I actually need the string position after the last "," .. and not containing the ",". Hope it makes sense.

3. all it remain to do is running a SUBSTR on my initial string FROM the calculated position.

I haven't tested the query on large strings so I do not know how slow it is. So if someone actually tests it on a large string I would very happy to know the results.

Answer 4

In MySQL, this works if there are multiple '=' characters in the string

SUBSTRING(supplier_reference FROM (LOCATE('=',supplier_reference)+1))

It return the substring after(+1) having found the the first =

Answer 5

For SQL Management studio I used a variation of BWS' answer. This gets the data to the right of '=', or NULL if the symbol doesn't exist:

   CASE WHEN (RIGHT(supplier_reference, CASE WHEN (CHARINDEX('=',supplier_reference,0)) = 0 THEN
    0 ELSE CHARINDEX('=', supplier_reference) -1 END)) <> '' THEN (RIGHT(supplier_reference, CASE WHEN (CHARINDEX('=',supplier_reference,0)) = 0 THEN
    0 ELSE CHARINDEX('=', supplier_reference) -1 END)) ELSE NULL END

Answer 6

Try this (it should work if there are multiple '=' characters in the string):

SELECT RIGHT(supplier_reference, (CHARINDEX('=',REVERSE(supplier_reference),0))-1) FROM ps_product