Sort String As Number In Sql Server
Solution 1:
Judicious use of REVERSE, CHARINDEX, and SUBSTRING, can get us what we want. I have used hopefully-explanatory columns names in my code below to illustrate what's going on.
Set up sample data:
DECLARE@InvoiceTABLE (
InvoiceNumber nvarchar(10)
);
INSERT@InvoiceVALUES
('790711')
,('790709-1')
,('790709-11')
,('790709-21')
,('790709-212')
,('790709-2')
SELECT*FROM@InvoiceSample data:
InvoiceNumber
-------------
790711
790709-1
790709-11
790709-21
790709-212
790709-2
And here's the code. I have a nagging feeling the final expressions could be simplified.
SELECT
InvoiceNumber
,REVERSE(InvoiceNumber)
AS Reversed
,CHARINDEX('-',REVERSE(InvoiceNumber))
AS HyphenIndexWithinReversed
,SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))
AS ReversedWithoutAffix
,SUBSTRING(InvoiceNumber,1+LEN(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))),LEN(InvoiceNumber))
AS AffixIncludingHyphen
,SUBSTRING(InvoiceNumber,2+LEN(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))),LEN(InvoiceNumber))
AS AffixExcludingHyphen
,CAST(
SUBSTRING(InvoiceNumber,2+LEN(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))),LEN(InvoiceNumber))
AS int)
AS AffixAsInt
,REVERSE(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber)))
AS WithoutAffix
FROM @Invoice
ORDER BY
-- WithoutAffix
REVERSE(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber)))
-- AffixAsInt
,CAST(
SUBSTRING(InvoiceNumber,2+LEN(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))),LEN(InvoiceNumber))
AS int)
Output:
InvoiceNumber Reversed HyphenIndexWithinReversed ReversedWithoutAffix AffixIncludingHyphen AffixExcludingHyphen AffixAsInt WithoutAffix
------------- ---------- ------------------------- -------------------- -------------------- -------------------- ----------- ------------
790709-1 1-907097 2 907097 -1 1 1 790709
790709-2 2-907097 2 907097 -2 2 2 790709
790709-11 11-907097 3 907097 -11 11 11 790709
790709-21 12-907097 3 907097 -21 21 21 790709
790709-212 212-907097 4 907097 -212 212 212 790709
790711 117097 0 117097 0 790711
Note that all you actually need is the ORDER BY clause, the rest is just to show my working, which goes like this:
- Reverse the string, find the hyphen, get the substring after the hyphen, reverse that part: This is the number without any affix
- The length of (the number without any affix) tells us how many characters to drop from the start in order to get the affix including the hyphen. Drop an additional character to get just the numeric part, and convert this to
int. Fortunately we get a break from SQL Server in that this conversion gives zero for an empty string. - Finally, having got these two pieces, we simple
ORDER BY(the number without any affix) and then by (the numeric value of the affix). This is the final order we seek.
The code would be more concise if SQL Server allowed us to say SUBSTRING(value, start) to get the string starting at that point, but it doesn't, so we have to say SUBSTRING(value, start, LEN(value)) a lot.
Solution 2:
Try this one -
Query:
DECLARE@InvoiceTABLE (InvoiceNumber VARCHAR(10))
INSERT@InvoiceVALUES
('790711')
, ('790709-1')
, ('790709-21')
, ('790709-11')
, ('790709-211')
, ('790709-2')
;WITH cte AS
(
SELECT
InvoiceNumber
, lenght = LEN(InvoiceNumber)
, delimeter = CHARINDEX('-', InvoiceNumber)
FROM@Invoice
)
SELECT InvoiceNumber
FROM cte
CROSSJOIN (
SELECT repl =MAX(lenght - delimeter)
FROM cte
WHERE delimeter !=0
) mx
ORDERBYSUBSTRING(InvoiceNumber, 1, ISNULL(NULLIF(delimeter -1, -1), lenght))
, RIGHT(REPLICATE('0', repl) +SUBSTRING(InvoiceNumber, delimeter +1, lenght), repl)
Output:
InvoiceNumber
-------------
790709-1
790709-2
790709-11
790709-21
790709-211
790711
Solution 3:
Try this
SELECT invoiceid FROM Invoice
ORDERBYCASEWHEN PatIndex('%[-]%',invoiceid) >0THENLEFT(invoiceid,PatIndex('%[-]%',invoiceid)-1)
ELSE invoiceid END*1
,CASEWHEN PatIndex('%[-]%',REVERSE(invoiceid)) >0THENRIGHT(invoiceid,PatIndex('%[-]%',REVERSE(invoiceid))-1)
ELSENULLEND*1Above query uses two case statements
- Sorts first part of Invoiceid 790109-1 (eg: 790709)
- Sorts second part of Invoiceid after splitting with '-' 790109-1 (eg: 1)
For detailed understanding check the below SQLfiddle
OR use 'CHARINDEX'
SELECT invoiceid FROM Invoice
ORDERBYCASEWHEN CHARINDEX('-', invoiceid) >0THENLEFT(invoiceid, CHARINDEX('-', invoiceid)-1)
ELSE invoiceid END*1
,CASEWHEN CHARINDEX('-', REVERSE(invoiceid)) >0THENRIGHT(invoiceid, CHARINDEX('-', REVERSE(invoiceid))-1)
ELSENULLEND*1Solution 4:
Order by each part separately is the simplest and reliable way to go, why look for other approaches? Take a look at this simple query.
select*from Invoice
orderbyConvert(int, SUBSTRING(invoiceid, 0, CHARINDEX('-',invoiceid+'-'))) asc,
Convert(int, SUBSTRING(invoiceid, CHARINDEX('-',invoiceid)+1, LEN(invoiceid)-CHARINDEX('-',invoiceid))) ascSolution 5:
Plenty of good answers here, but I think this one might be the most compact order by clause that is effective:
SELECT*FROM Invoice
ORDERBYLEFT(InvoiceId,CHARINDEX('-',InvoiceId+'-'))
,CAST(RIGHT(InvoiceId,CHARINDEX('-',REVERSE(InvoiceId)+'-'))ASINT)DESCDemo: - SQL Fiddle
Note, I added the '790709' version to my test, since some of the methods listed here aren't treating the no-suffix version as lesser than the with-suffix versions.
If your invoiceID varies in length, before the '-' that is, then you'd need:
SELECT*FROM Invoice
ORDERBYCAST(LEFT(list,CHARINDEX('-',list+'-')-1)ASINT)
,CAST(RIGHT(InvoiceId,CHARINDEX('-',REVERSE(InvoiceId)+'-'))ASINT)DESCDemo with varying lengths before the dash: SQL Fiddle
Post a Comment for "Sort String As Number In Sql Server"