BillPowell
asked on
Need a Sql Server alternative to Acess Val function
Just like the tile suggests, I need a function in sql server that will allow me to order char columns like this:
1
2
3
4
10
instead of
1
10
2
3
4
1
2
3
4
10
instead of
1
10
2
3
4
ASKER
I dont get it. Is there no function that does this, I have to fill a temp table?
Nope, that was just a sample, all you need to do is sort on the column after casting it as an integer datatype
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Also you may want to cast as a decimal data type if not all the numbers are whole numbers, I think you would have to change ScottPletchers constraint to the isnumeric function rather than like:
SELECT *
FROM yourTable
WHERE ISNUMERIC(charColumn) = 1
ORDER BY CAST(charColumn AS decimal)
SELECT *
FROM yourTable
WHERE ISNUMERIC(charColumn) = 1
ORDER BY CAST(charColumn AS decimal)
Be very careful of ISNUMERIC(); for example, try the following:
SELECT ISNUMERIC(',')
SELECT ISNUMERIC('.,')
SELECT ISNUMERIC('3D4')
SELECT ISNUMERIC('.4E7')
SELECT ISNUMERIC('$334')
SELECT ISNUMERIC(',')
SELECT ISNUMERIC('.,')
SELECT ISNUMERIC('3D4')
SELECT ISNUMERIC('.4E7')
SELECT ISNUMERIC('$334')
Blimey!
Does this do what you want?
CREATE TABLE #temp1 (col1 varchar(10))
GO
INSERT INTO #temp1 VALUES(1)
INSERT INTO #temp1 VALUES(2)
INSERT INTO #temp1 VALUES(3)
INSERT INTO #temp1 VALUES(4)
INSERT INTO #temp1 VALUES(10)
GO
SELECT col1 FROM #temp1 ORDER by CAST(col1 AS INT)