Solved

Need a Sql Server alternative to Acess Val function

Posted on 2004-09-21
7
974 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 125 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 69

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

856 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