SQL: numeric sort of a varchar field

problem is that a bus line can contain character

eg. line "108" and "108A" and this must be sorted as 108 and then 108S


1
1A
2
34
3B
3BC
4


"Paul Shapiro" a écrit dans le message de
groupe de discussion : #Ou5e3OXIHA.2000@TK2MSFTNGP05.phx.gbl...
> Use Order By Cast(columnName as int), as long as all values are legitimate
> integers. But if so, why not make the column datatype int?
>
> wrote in message
>
>> Hello Everybody,
>>
>> my question is the following. how can sort numerically a sql query with
>> varchar field ?
>
 >> Stay informed about: SQL: numeric sort of a varchar field 

That's harder. All I can think of is to write a function which finds the
first non-numeric character position. Sort by the numeric value of the
digits, and then by the remaining alpha portion.

wrote in message

> problem is that a bus line can contain character
>
> eg. line "108" and "108A" and this must be sorted as 108 and then 108S
>
>
> 1
> 1A
> 2
> 34
> 3B
> 3BC
> 4
>
>
> "Paul Shapiro" a écrit dans le message de
> groupe de discussion : #Ou5e3OXIHA.2000@TK2MSFTNGP05.phx.gbl...
>> Use Order By Cast(columnName as int), as long as all values are
>> legitimate integers. But if so, why not make the column datatype int?
>>
>> wrote in message
>>
>>> Hello Everybody,
>>>
>>> my question is the following. how can sort numerically a sql query with
>>> varchar field ?
>>
 >> Stay informed about: SQL: numeric sort of a varchar field 

Your best bet is to separate out the numeric part from the character part
into two separate columns. Here's a simple sample query that you could use
as a basis for that. It's not going to be extremely efficient - I'd
recommend splitting them up one time permanently if I were you:

CREATE TABLE #t (n VARCHAR(100))
INSERT INTO #t (n)
SELECT '1'
UNION SELECT '1A'
UNION SELECT '2'
UNION SELECT '34'
UNION SELECT '3B'
UNION SELECT '3BC'
UNION SELECT '4'
GO

WITH Numbers (num)
AS
(
SELECT 1
UNION ALL
SELECT num + 1
FROM Numbers
WHERE num < 100
)
SELECT CAST(SUBSTRING(#t.n, 1, MIN(num) - 1) AS INTEGER) AS NumPart,
SUBSTRING(#t.n, MIN(num), 255) AS AlphaPart
FROM Numbers
CROSS APPLY #t
WHERE SUBSTRING(#t.n + 'Z', num, 1) BETWEEN 'A' AND 'Z'
GROUP BY #t.n;


"Paul Shapiro" wrote in message

> That's harder. All I can think of is to write a function which finds the
> first non-numeric character position. Sort by the numeric value of the
> digits, and then by the remaining alpha portion.
>
> wrote in message
>
>> problem is that a bus line can contain character
>>
>> eg. line "108" and "108A" and this must be sorted as 108 and then 108S
>>
>>
>> 1
>> 1A
>> 2
>> 34
>> 3B
>> 3BC
>> 4
>>
>>
>> "Paul Shapiro" a écrit dans le message de
>> groupe de discussion : #Ou5e3OXIHA.2000@TK2MSFTNGP05.phx.gbl...
>>> Use Order By Cast(columnName as int), as long as all values are
>>> legitimate integers. But if so, why not make the column datatype int?
>>>
>>> wrote in message
>>>
>>>> Hello Everybody,
>>>>
>>>> my question is the following. how can sort numerically a sql query with
>>>> varchar field ?
>>>
>
 >> Stay informed about: SQL: numeric sort of a varchar field