SQL - Order by after specific sign

Question

I have a result of an Select-Statement with strings in this form:

"123400000000020~forename~surname~P50206.00011" 
"123400000111111~forname1~surname1~P50206.00011"  
....

How I can order by the surnames in SQL? Is their a possibilty to cut the strings @ the 2nd "~"?

Don't store data like that, it will only cause you lots of trouble! Have separate columns for the different values. — jarlh, Jan 13 '16 at 14:00
If you are using SQL Server: http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x — nicomp, Jan 13 '16 at 14:00
thank you, we changed the way of storing the data, much easier :) — Lustknylch, Jan 20 '16 at 07:45

LDMJoe · Accepted Answer · 2016-01-13T14:46:36.380

CHARINDEX is your friend here. You're going to need to nest it within itself and run it twice because you want the second instance.

DECLARE @SearchString nvarchar(max)
SET @SearchString = '123400000111111~forname1~surname1~P50206.00011'

DECLARE @Delimiter nvarchar(1)
SET @Delimiter = '~'

/*
SELECT @SearchString AS [Your String]
--the string puked back

SELECT 
    RIGHT
        (
        @SearchString, 
        LEN(@SearchString) - charindex(@Delimiter, @SearchString)
        ) AS [The First Instance]
--first instance of @Delimiter
*/  

SELECT 
    RIGHT
    (
        RIGHT
            (
            @SearchString, 
            LEN(@SearchString) - CHARINDEX(@Delimiter, @SearchString)
            ), 
            LEN
            (
                RIGHT(@SearchString, LEN(@SearchString) - CHARINDEX(@Delimiter, @SearchString))
            ) - 
            CHARINDEX
            (
                @Delimiter, RIGHT(@SearchString, LEN(@SearchString) - CHARINDEX(@Delimiter, @SearchString))
            )
    ) AS [Your Awful Order By]
--what you're after

But for the love of all that is sacred in the world of databases as well as the sake of your own sanity, not to mention performance, do not store data like this!

thanks like i said we saved the data now in seperate columns. But i will try this above. — Lustknylch, Jan 20 '16 at 07:45

score 0 · Answer 2 · answered Jan 13 '16 at 14:50

I think this will be help you ;

select substr(somedata, regexp_instr(somedata, '(~)', 1, 2, 1)  ,    regexp_instr(substr(somedata, regexp_instr(somedata, '(~)', 1, 2, 1) ),      '(~)', 1, 1, 1)-2 ) as surname from (
select '123400000000020~forename~surname~P50206.00011' as somedata  from dual
  union
   select '123400000111111~forname1~surname1~P50206.00011' as somedata   from dual
union
select '123400000000020~forename~surname3~' as somedata  from dual
union
select '3~~surname5~P50206.00011' as somedata   from dual
)
order by 1 asc

SQL - Order by after specific sign

2 Answers2