• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1026
  • Last Modified:

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
0
BillPowell
Asked:
BillPowell
  • 4
  • 2
1 Solution
 
mcmonapCommented:
Hi BillPowell,

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)
0
 
BillPowellAuthor Commented:
I dont get it.  Is there no function that does this, I have to fill a temp table?
0
 
mcmonapCommented:
Nope, that was just a sample, all you need to do is sort on the column after casting it as an integer datatype
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Scott PletcherSenior DBACommented:
Just in case you have non-integer data in one column -- which is very likely if it's defined as char -- you may want to do this:


SELECT *
FROM yourTable
WHERE charColumn NOT LIKE '%[^0-9]%'
ORDER BY CAST(charColumn AS INT)
0
 
mcmonapCommented:
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)
0
 
Scott PletcherSenior DBACommented:
Be very careful of ISNUMERIC(); for example, try the following:

SELECT ISNUMERIC(',')
SELECT ISNUMERIC('.,')
SELECT ISNUMERIC('3D4')
SELECT ISNUMERIC('.4E7')
SELECT ISNUMERIC('$334')
0
 
mcmonapCommented:
Blimey!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now