CODEWITHSUNDEEP
mysqlsqlsubstring
Aparichith
Aparichith

Reputation: 1535

How to use MySQL string operation?

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?

Upvotes: 2

Views: 237

Answers (3)

Dilanyan
Dilanyan

Reputation: 33

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.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311188

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

Upvotes: 2

Teetrinker
Teetrinker

Reputation: 575

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.

Upvotes: 1

Related Questions