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

Nascent

What database engine are you using? It's not SQL Server, right? – Hinduism

See this answer: https://mcmap.net/q/131476/-last-index-of-a-given-substring-in-mysql – Audieaudience

Answer 1

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

Please use this for further reference.

Hamitosemitic

That can cause some problems, it there's no '=' in the column, in that case you can use: SELECT IF(LOCATE('=', supplier_reference), SUBSTRING_INDEX(supplier_reference,'=',-1), default_value) FROM ps_product; and you can set a default value instead returning the original full column. – Arabinose

What if string contains multiple '=' and I want everything after first '='? – Swastika

This is not entirely correct. To be able to set the right negative index you need to know exactly how many times the searched string occurs in the subject. For that this might help: https://mcmap.net/q/116474/-count-the-number-of-occurrences-of-a-string-in-a-varchar-field – Audieaudience

Answer 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

Kenney

CHARINDEX doesn't exist in MySQL which the question was about. The MySQL alternative is LOCATE. – Audieaudience

Answer 3

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 =

Physiotherapy

This was useful when there are multiple '=' and we want string after first occurrence of '=' – Swastika

Answer 4

Try this in MySQL.

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

Mutz

Thanks for your contribution. It would be more helpful to add some explanation of how this solves the problem. – Twig

Answer 5

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'

Eyeglass

To select to the left Edit: it's 1 (positive) not -1 – Athallia

Thank you @Eolia. I have corrected the typo – Eyeglass

Answer 6

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.

Demolish

Answer 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

Dagney

Answer 8

SELECT NULLIF(SUBSTRING_INDEX(column, '=', -1), column)

Visby