March 31, 2006 at 5:52 am
Hi folks
Seems strange that you can't order a query by using a val function as you can in access 2003.
Eg.
A column of address data stored as text and you want to sort on ascending house numbers there is no way to do it in SQL Server but it's easlily achived with Access Val() function.
Seems strange.
Paul
April 3, 2006 at 8:00 am
This was removed by the editor as SPAM
April 3, 2006 at 10:42 am
You will find that SQL Server is pretty flexible. For example, in SQL Server 2005 you can add your own functions using VB.Net. While the performance won't be as good in this case, you can also create a function using just T-SQL to do the job. Here is a function written in T-SQL that does the same thing.
First, an example of how it can be used:
select * from Address
order by dbo.VAL(AddressLine1)
Now the actual code:
CREATE FUNCTION dbo.VAL(@value varchar(100))
RETURNS decimal
AS
begin
declare @char char(1)
declare @length int
declare @newValue varchar(100)
declare @position int
set @newValue = ''
set @length = len(rtrim(@value))
set @position = 1
set @char = substring(@value,@position,1)
while ascii(@char) in (32,44,46,36) or ascii(@char) between 48 and 57 begin
if ascii(@char) between 48 and 57 begin
set @newValue = @newValue + @char
end
set @position = @position + 1
set @char = substring(@value,@position,1)
end
if len(@newValue) = 0 begin
set @newValue = '0'
end
return convert(decimal,@newValue)
end
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
April 3, 2006 at 12:51 pm
I just realized my function has a two flaws. Try this one instead:
create FUNCTION VAL(@value varchar(100))
RETURNS decimal
AS
begin
if @value is null return null
declare @char char(1)
declare @length int
declare @newValue varchar(100)
declare @position int
set @value = @value + '_'
set @newValue = ''
set @length = len(rtrim(@value))
set @position = 1
set @char = substring(@value,@position,1)
while ascii(@char) in (32,44,46,36) or ascii(@char) between 48 and 57 begin
if ascii(@char) between 48 and 57 or @char = '.' begin
set @newValue = @newValue + @char
end
set @position = @position + 1
set @char = substring(@value,@position,1)
end
if len(@newValue) = 0 begin
set @newValue = '0'
end
return convert(decimal,@newValue)
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply