?
Solved

Need a Sql Server alternative to Acess Val function

Posted on 2004-09-21
7
Medium Priority
?
1,017 Views
Last Modified: 2008-06-17
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
Comment
Question by:BillPowell
  • 4
  • 2
7 Comments
 
LVL 15

Expert Comment

by:mcmonap
ID: 12113256
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
 
LVL 11

Author Comment

by:BillPowell
ID: 12114881
I dont get it.  Is there no function that does this, I have to fill a temp table?
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 12115458
Nope, that was just a sample, all you need to do is sort on the column after casting it as an integer datatype
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 12115632
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
 
LVL 15

Expert Comment

by:mcmonap
ID: 12115804
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 12115860
Be very careful of ISNUMERIC(); for example, try the following:

SELECT ISNUMERIC(',')
SELECT ISNUMERIC('.,')
SELECT ISNUMERIC('3D4')
SELECT ISNUMERIC('.4E7')
SELECT ISNUMERIC('$334')
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 12116058
Blimey!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question