How to use MySQL string operation?

Question

i want to know how can i use MySQL string functions on my DB.

I have MySQL db with following like data

+---+-----------------------+
|id | name                  |
+---+-----------------------+
| 1 | /sun/steave/xyz       |
| 2 | /mon/alan/asdsas      |
| 3 | /sun/mark/we          |
| 4 | /wed/john/rtd         |
| 5 | /thu/mich/dfgsd       |
+---+-------------------   -+

where name is of type varchar(255).

i want to select only name i,e (/sun/steave/xyz).

i tried like

select substr(name,4) from my_table;

(i can't use length in substring, like (name,4,6) because name is dynamic)

which returns me

steave/xyz 
alan/asdsas
mark/we
john/rtd
mich/dfgsd

How can i select only names from my table? Is that possible through MySQL string functions?

Using 'instr()', you can find out, where the '/' is in your remaining string. Together with 'len()' this will allow you to cut out the name. dev.mysql.com/doc/refman/5.0/en/… (But it would be much better NOT to have multiple data in one database column. Have a look for 'normalization'.) — Teetrinker
– Teetrinker, Commented Oct 13, 2014 at 10:10

Mureinik · Accepted Answer · 2014-10-13 10:43:17Z

2

You can use a couple substring_index calls to cut your string between the /s:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(name, '/', 3), '/', -1)
FROM   my_table

EDIT:
As requested in the comments, some more details. To quote the documentation on substring_index:

SUBSTRING_INDEX(str,delim,count) Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned.

Let's take the string '/sun/steave/xyz' as an example. The inner substring_idex call returns the substring before the 3rd /, so for our case, it returns '/sun/steave'. The outer substring_index returns the substring after the last '/', so given '/sun/steave' it will return just 'steave'.

edited Oct 13, 2014 at 10:43

answered Oct 13, 2014 at 10:11

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aparichith Over a year ago

Thanks for the answer. can you please add some more explanation to your answer? it will be more useful for me like learners. thank you.

Mureinik Over a year ago

@h.APP.y Edited my answer with some explanation, hope that's what you were looking for.

Teetrinker · Accepted Answer · 2014-10-13 10:15:25Z

1

Something along the lines of

select left(right(string.name, length(string.name) - 5), instr( right(string.name, length(string.name) - 5), '/')-1) from (select "/sun/steave/xyz" as name) string

will work.

answered Oct 13, 2014 at 10:15

Teetrinker

5755 silver badges15 bronze badges

Comments

Dilanyan · Accepted Answer · 2014-10-13 11:50:51Z

1

This can be easily done in XML:

SELECT
 MyXML.id
,MyXML.name
,x.value('/NAME[1]/PART[2]','VARCHAR(255)') AS 'PART2'
,x.value('/NAME[1]/PART[3]','VARCHAR(255)') AS 'PART3'
,x.value('/NAME[1]/PART[4]','VARCHAR(255)') AS 'PART4'
FROM (
    SELECT Id, Name
    ,CONVERT(XML,'<NAME><PART>' + REPLACE(Name,'/', '</PART><PART>') + '</PART></NAME>') AS X 
    FROM my_table 
) MyXML

Anyway, you should rethink your table structure.

edited Oct 13, 2014 at 11:50

answered Oct 13, 2014 at 10:45

Dilanyan

335 bronze badges

3 Comments

Teetrinker Over a year ago

AFAIK, there is no "with" in MySQL. ( bugs.mysql.com/bug.php?id=16244 ) And using XML to manipulate a string is quite a creative approach in my view.

Dilanyan Over a year ago

@Teetrinker, thanks for your comment, I corrected the code. XML code easier to understand than a cascading functions IMHO.

Teetrinker Over a year ago

I found your approach interesting and wanted to evaluate its performance compared to pure string manipulation and was forced to notice that mysql's convert()-function doesn't do what you assumed. It is meant for character set conversion. dev.mysql.com/doc/refman/5.1/en/charset-convert.html Did you even test run your answer?

Collectives™ on Stack Overflow

How to use MySQL string operation?

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related