'SQL SELECT everything after a certain character

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
mysqlsqlsubstringstring-length


Solution 1:[1]

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

Please use this for further reference.

Solution 2:[2]

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

Solution 3:[3]

Try this in MySQL.

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

Solution 4:[4]

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

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

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

Solution 5:[5]

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.

  1. 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.

Solution 6:[6]

If your string is

str = 'abc=def=ghi'

To select to the right:

select substring_index(str,'=',-1) from tablename ==> result is 'ghi'
select substring_index(str,'=',-2) from tablename ==> result is 'def=ghi'

To select to the left

select substring_index(str,'=',-1) from tablename ==> result is 'abc'

select substring_index(str,'=',2) from tablename ==> result is 'abc=def'

Solution 7:[7]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Osama Rizwan
Solution 2 VladL
Solution 3
Solution 4
Solution 5 Rosu Flavius
Solution 6 Birhan Nega
Solution 7 Jacob Morris

Related Questions