StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Judicious use of <code>REVERSE</code>, <code>CHARINDEX</code>, and <code>SUBSTRING</code>, can get us what we want. I have used hopefully-explanatory columns names in my code below to illustrate what's going on.</p> <p>Set up sample data:</p> <pre><code>DECLARE @Invoice TABLE ( InvoiceNumber nvarchar(10) ); INSERT @Invoice VALUES ('790711') ,('790709-1') ,('790709-11') ,('790709-21') ,('790709-212') ,('790709-2') SELECT * FROM @Invoice </code></pre> <p>Sample data:</p> <pre><code>InvoiceNumber ------------- 790711 790709-1 790709-11 790709-21 790709-212 790709-2 </code></pre> <p>And here's the code. I have a nagging feeling the final expressions could be simplified.</p> <pre><code>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) </code></pre> <p>Output:</p> <pre><code>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 </code></pre> <p>Note that all you actually need is the <code>ORDER BY</code> clause, the rest is just to show my working, which goes like this:</p> <ul> <li>Reverse the string, find the hyphen, get the substring after the hyphen, reverse that part: This is the number without any affix</li> <li>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 <code>int</code>. Fortunately we get a break from SQL Server in that this conversion gives zero for an empty string.</li> <li>Finally, having got these two pieces, we simple <code>ORDER BY</code> (the number without any affix) and then by (the numeric value of the affix). This is the final order we seek.</li> </ul> <p>The code would be more concise if SQL Server allowed us to say <code>SUBSTRING(value, start)</code> to get the string starting at that point, but it doesn't, so we have to say <code>SUBSTRING(value, start, LEN(value))</code> a lot.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload