Question

ojoc on Wed, 12 Jul 2017 00:14:56


<g class="gr_ gr_54 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" data-gr-id="54" id="54">Hi</g> I am very new to SQL and I have a query where I need to list values of columns in <g class="gr_ gr_50 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="50" id="50">asc</g> order.

Eg: 

121-P1

121-P2

121-P3

.

.

.

121-P10

121-P11

However, I get the list as below:

121-P1

121-P10

121-P11

.

.

121-P16

121-P2

121-P3

How do I list it in <g class="gr_ gr_468 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" data-gr-id="468" id="468">correct</g> order?


Sponsored



Replies

Lin Leng on Wed, 12 Jul 2017 08:21:15


Hi ojoc,

You just need to modify your ORDER BY clause a little bit, here’s an example:
CREATE TABLE [dbo].[TEST] ---CREATE TEST TABLE
(
    [C1] [VARCHAR](50) NULL
) ON [PRIMARY]
GO

--Insert data to table 

--

SELECT C1
FROM dbo.TEST
ORDER BY CAST(SUBSTRING(C1, CHARINDEX('P',C1)+1,LEN(C1)-CHARINDEX('P',C1)) AS INT) --Add this order by clause to yout query

If you have any other questions, please let me know.

Regards,
Lin

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.