i have the data like this:
CODE_VD
N_10_19_xxx
N_0_3_xxx
N_121_131_xxx
N_100_120_xxx
N_80_90_xxx
N_20_29_xxx
as you can see i need to sort just the first number after N_,i don't know how can i get this number.
i have tried with susbsting(CODE_VD,2,3) but not exactly what i expected.
i want to get this:
CODE_VD
N_0_3_xxx
N_10_19_xxx
N_20_29_xxx
N_80_90_xxx
N_100_120_xxx
N_121_131_xxx
how can i do that ?
DECLARE @MyTable TABLE
(
CODE_VD VARCHAR(20)
)
INSERT INTO @MyTable
( CODE_VD )
VALUES
('N_10_19_xxx'),
('N_0_3_xxx'),
('N_121_131_xxx'),
('N_100_120_xxx'),
('N_80_90_xxx'),
('N_20_29_xxx');
SELECT * FROM
(
SELECT
*,
CONVERT(INT,
SUBSTRING(mt.CODE_VD,
3,
CHARINDEX('_', mt.CODE_VD, 3) - 3)) ConvCol
FROM @MyTable mt
) mt
ORDER BY mt.ConvCol
I converted to int to get the sort to work correctly, because 100 > 20
SELECT SUBSTRING(CODE_VD,3, CHARINDEX('_',CODE_VD, 3)-3)
declare @t Table (CODE_VD VARCHAR(MAX))
INSERT INTO @t (CODE_VD)VALUES ('N_10_19_xxx')
INSERT INTO @t (CODE_VD)VALUES ('N_0_3_xxx')
INSERT INTO @t (CODE_VD)VALUES ('N_121_131_xxx')
INSERT INTO @t (CODE_VD)VALUES ('N_100_120_xxx')
;WITH
sorted
AS
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY SUBSTRING(CODE_VD,3, CHARINDEX('_',CODE_VD, 3)-3) ORDER BY CODE_VD) AS sequence_id
FROM
@t
)
SELECT
CODE_VD
FROM
sorted
WHERE
sequence_id = 1
