1

I am new to SQL .. I wanted to sort one column , the values of that column will be Example : D2V2PRT1,D1V2PRT2,D2V1PRT1,D1V1PRT3......

I want sorted output as 

D1V1PRT3,
D1V2PRT2,
D2V1PRT1,
D2V2PRT1,......

so the sorting should happen like , first it will sort for D values then for V values and then for PRT values, all the values are in string.

I have written some logic and able to separate the values from D V and PRT , so now my question is how to specify this in order by

Thanks in Advance

Improve this question
3
  • Can the numbers go above 9? Also, post the logic that separates the D V PRT values. Commented Mar 22, 2014 at 10:57
  • no all will be sorted in Asc and yes there are no limits for those no Commented Mar 22, 2014 at 11:27
  • @Andomar for getting values after D CAST(substring(PRT.DisplayID,(CHARINDEX('D',PRT.DisplayID)+1),(CHARINDEX('V',PRT.DisplayID)-(CHARINDEX('D',PRT.DisplayID)+1))) as numeric) logic am using ,, for V values CAST(substring(PRT.DisplayID,(CHARINDEX('V',PRT.DisplayID)+1),(CHARINDEX('PRT',PRT.DisplayID)-CHARINDEX('V',PRT.DisplayID)-1))as numeric) asc and for PRT CAST(substring(PRT.DisplayID,(CHARINDEX('PRT',PRT.DisplayID)+3),len(PRT.DisplayID)) as numeric) asc using the above logic am able to get the nos Commented Mar 22, 2014 at 11:36

2 Answers 2

Reset to default
2

You can use charindex to extract the numbers:

declare @t table (id varchar(100));
insert @t values ('D1V1PTR1'), ('D100V1PTR1'), ('D1V8PTR5'), ('D1V40PTR10');

; with  extracted as
        (
        select  substring(id, 2, charindex('V', id)-2) as D
        ,       substring(id, charindex('V', id)+1, 
                              charindex('PTR', id)-charindex('V', id)-1) as V
        ,       substring(id, charindex('PTR', id)+3, 100) as PTR
        from    @t
        )
select  *
from    extracted
order by
        cast(d as bigint)
,       cast(v as bigint)
,       cast(ptr as bigint)

Example at SQL Fiddle.

As you can see, SQL is not the best language for parsing strings. :)

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

2 Comments

as i am new to this web site so it is not allowing me to mark ur ans as correct ans. Sorry
@Jaydeeprony89 You should be able to accept answers regardless of reputation, however you can not vote up until 15 reputation.
1

You sort on partial strings using ORDER BY SUBSTRING(...), like this:

order by substring(val, 1, 2), substring(val, 3, 2), substring(val, 5, 4)

This assumes the D and V fields are two chars long and PRT four chars and sorts on the char string (D1and V1and so on), not the numeric value, which might not be correct, but as you already have logic to split the string it should be easy to adjust.

Improve this answer

5 Comments

D1V14PRT11 D1V14PRT9 D1V19PRT26 D1V14PRT1 D1V14PRT6 D1V14PRT23 D1V14PRT13 D1V14PRT2 D1V14PRT22 D11V15PRT15 D189V19PRT7 D1V14PRT14 D1V14PRT7 D1V14PRT5 D1V14PRT4 D189V18PRT12 D1V14PRT3 D189V19PRT9 D1V14PRT10 D1V14PRT24 D1V22PRT27 D1V14PRT20 D1V14PRT8 D1V14PRT19 D1V14PRT12 D1V14PRT25 D1V14PRT18 D184V10PRT3 D188V14PRT2 D189V19PRT6 D1V14PRT21 D189V19PRT8 D23V11PRT1 D189V18PRT11 D1V14PRT17 D189V18PRT4 D189V18PRT5 D1V14PRT16 D189V19PRT10 this is the sample of my column, i want it to sort Based on DascnoVascnoPRTascno
@Jaydeeprony89 Seeing as the length of the parts in the string can vary, you have to use some custom logic to extract them. Take a look at the answer posted by Andomar
Yes am able to extract the values after D V and PRT but in order by what i need to mentioned?
@Jaydeeprony89 Andomars answer is probably what you want.
Yes got it.. even i was also almost reached there some how but Yes Andomar ans what i was looking for

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.