Solved

Need a Sql Server alternative to Acess Val function

Posted on 2004-09-21
7
980 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

763 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