Solved

Need a Sql Server alternative to Acess Val function

Posted on 2004-09-21
7
941 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 69

Accepted Solution

by:
ScottPletcher 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:ScottPletcher
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

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.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now