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

What database engine are you using? It's not SQL Server, right? — acfrancis, Oct 21 '13 at 13:15
See this answer: https://stackoverflow.com/a/18422739/2713729 — Janne Annala, Nov 23 '18 at 13:12

score 106 · Accepted Answer · edited Nov 01 '22 at 13:05

106

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

Please use this for further reference.

edited Nov 01 '22 at 13:05

General Grievance

4,555
31
31
45

answered Oct 21 '13 at 13:19

Gaurav Pant

4,029
6
31
54

13

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. – golddragon007 Aug 10 '16 at 11:19
4

What if string contains multiple '=' and I want everything after first '='? – Mayur Patel Nov 03 '17 at 10:14
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://stackoverflow.com/a/12344881/2713729 – Janne Annala Nov 23 '18 at 12:22

score 9 · Answer 2 · edited Oct 21 '13 at 13:22

9

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

edited Oct 21 '13 at 13:22

VladL

12,769
10
63
83

answered Oct 21 '13 at 13:19

BWS

3,786
18
25

2

CHARINDEX doesn't exist in MySQL which the question was about. The MySQL alternative is LOCATE. – Janne Annala Nov 23 '18 at 12:24

score 7 · Answer 3 · edited Jun 04 '15 at 23:26

7

Try this in MySQL.

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

edited Jun 04 '15 at 23:26

answered Jun 04 '15 at 22:30

Mohsin

71
1
1

1

Thanks for your contribution. It would be more helpful to add some explanation of how this solves the problem. – Jun 04 '15 at 23:01

Ludo · Answer 4 · 2020-03-17T18:33:56.767

7

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 =

edited Mar 17 '20 at 18:33

answered Sep 18 '17 at 15:39

Ludo

743
1
10
25

This was useful when there are multiple '=' and we want string after first occurrence of '=' – Mayur Patel Nov 03 '17 at 10:29

score 3 · Answer 5 · edited Apr 09 '22 at 02:49

3

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'

edited Apr 09 '22 at 02:49

Birhan Nega

663
12
24

answered Apr 08 '22 at 18:10

Anamika

31
3

score 1 · Answer 6 · answered Oct 17 '18 at 20:23

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

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 ",".
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.

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.

score 0 · Answer 7 · answered Nov 21 '16 at 15:40

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

score 0 · Answer 8 · answered Feb 19 '23 at 17:46

0

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

answered Feb 19 '23 at 17:46

gzzz

349
3
7

SQL SELECT everything after a certain character

8 Answers8

Linked

Related