November 14, 2007 at 3:02 am
Hi,
I'm looking for the solution of the problem "sorting string"
ex:
I have in database values (as varchar) : 1A, 2A, 30, 10, 10B, 2, 3
when i sort this i receive : 10, 10B, 1A, 2, 2A, 3, 30
how can i sort this to get : 1A, 2, 2A, 3, 10, 10B, 30
November 14, 2007 at 3:27 am
One way of doing this is to create a user defined function that takes such a string (which looks like a house number :)) and returns a similar one but:
1: returns a string that is padded, and all numbers have the same length, followed by the letters, e.g. it
changes 1A to let's say '000001A'
2: it returns a number (the original number + order_of_letter*0.03, so 1A would be 1.03; 23.B would be 23.06, etc.
You can then order based on this function result. Note that this is expensive, but this function could work with more complex rules (e.g. can consider house number ranges, assuming that you are using this for house numbers :))
Regards,
Andras
November 21, 2007 at 2:48 am
using replicate function and u can solve it
November 22, 2007 at 2:33 pm
dgvsbabu (11/21/2007)
using replicate function and u can solve it
What it this replicate function? Is this something specific to SQL Server 2005?
In any case, it is easy to solve making the assumption that you have a number of at most 5 digits which is optionally followed by the letters A-F.
Here we go:
[Code]
drop table house
go
create table house(hn varchar(6))
go
insert into house values('1A')
insert into house values('2')
insert into house values('2A')
insert into house values('3')
insert into house values('10')
insert into house values('10B')
insert into house values('30')
go
select *
from
(
select hn,
case when charindex(right(hn,1),'ABCDEF')>0
then substring('00000',1,6-len(hn))
else substring('00000',1,5-len(hn))
end+hn normalized_hn
from house
) h
order by normalized_hn
[/Code]
November 22, 2007 at 5:44 pm
dgvsbabu (11/21/2007)
using replicate function and u can solve it
Well? No code? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2007 at 6:14 pm
If you make no major assumption about the string other than it's going to have digits on the left and non-digits on the right, you end up with something that you don't have to worry about, too much, if the length requirements change...
--===== Build a test table and some widely variable test data
-- THIS IS NOT PART OF THE SOLUTION.
CREATE TABLE TestData (SomeString VARCHAR(100))
INSERT INTO TestData (SomeString)
SELECT '1A' UNION ALL
SELECT '1ABCDEFGHIJKLMNOPQRSTUVYXYZ' UNION ALL
SELECT '2A' UNION ALL
SELECT '3' UNION ALL
SELECT '10' UNION ALL
SELECT '123456789ABCDEFGHIJKLMNOPQRSTUVYXYZ' UNION ALL
SELECT '2' UNION ALL
SELECT '2Z' UNION ALL
SELECT '10B' UNION ALL
SELECT '2C' UNION ALL
SELECT '30' UNION ALL
SELECT '123456789A' UNION ALL
SELECT '123456789012345678901234567890ABCDEFGHIJKLMNOPQRSTUVYXYZ'
--===== Display the data in sorted order as requested.
SELECT *
FROM TestData
ORDER BY
RIGHT(SPACE(100)+LEFT(SomeString,PATINDEX('%[^0-9]%',SomeString+' ')-1),100),
SUBSTRING(SomeString,PATINDEX(SomeString+' ','%[^0-9]%'),100)
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2007 at 6:41 pm
Here's a simple little function that will strip off the leading numeric values and return an INT that you can sort by. If the numeric leading digits aren't all INT's then the function should be changed to return a Decimal and the CONVERT statement should be changed to convert to Decimal.
CREATE FUNCTION fnNumAlphaFmt
(
@STR VARCHAR(10)
)
RETURNS INT
AS
BEGIN -- Start function
DECLARE
@i INT -- Index
, @Result INT -- Numeric result
, @StrLen INT -- Length of input string
, @TheNum VARCHAR(10) -- String the input into here
, @TheChar CHAR(1)
-- Assumes we want to return something that can be sorted that
-- started with numeric digits and then has alpha characters following.
-- Ex 1A, 10A, 2B, etc.
-- The idea is to strip of the leading numeric digits so they can be converted
-- to a number and then re-stringing the alpha portion.
SET @i = 1 -- First char of string
SET @StrLen = LEN(@Str)
SET @TheNum = ''
WHILE @i <= @StrLen
BEGIN
SET @TheChar = SUBSTRING(@Str, @i, 1)
IF @TheChar '9'
BREAK
SET @TheNum = @TheNum + @TheChar
SET @i = @i + 1
END
SET @Result = ISNULL(CONVERT(VARCHAR, @TheNum), 0)
RETURN @Result
END
GO
You can test it with this query:
SELECT
TheVal, SortVal
FROM
(SELECT '1A' AS TheVal, dbo.fnNumAlphaFmt('1A') AS SortVal
UNION SELECT '2A', dbo.fnNumAlphaFmt('2A')
UNION SELECT '3A', dbo.fnNumAlphaFmt('30')
UNION SELECT '10', dbo.fnNumAlphaFmt('10')
UNION SELECT '10B', dbo.fnNumAlphaFmt('10B')
UNION SELECT '2', dbo.fnNumAlphaFmt('2')
UNION SELECT '3', dbo.fnNumAlphaFmt('3')
) AS X
ORDER BY X.SortVal, X.TheVal
Good Luck,
Todd
November 22, 2007 at 7:43 pm
Test it I did...
Server: Msg 170, Level 15, State 1, Procedure fnNumAlphaFmt, Line 29
Line 29: Incorrect syntax near '9'.
If you believe that the numeric portion will never exceed that of an INT, then you still don't need the overhead of a UDF or WHILE loop...
DROP TABLE TestData
--===== Build a test table and some widely variable test data
-- THIS IS NOT PART OF THE SOLUTION.
CREATE TABLE TestData (SomeString VARCHAR(100))
INSERT INTO TestData (SomeString)
SELECT '1A' UNION ALL
SELECT '1ABCDEFGHIJKLMNOPQRSTUVYXYZ' UNION ALL
SELECT '2A' UNION ALL
SELECT '3' UNION ALL
SELECT '10' UNION ALL
SELECT '123456789ABCDEFGHIJKLMNOPQRSTUVYXYZ' UNION ALL
SELECT '2' UNION ALL
SELECT '2Z' UNION ALL
SELECT '10B' UNION ALL
SELECT '2C' UNION ALL
SELECT '30' UNION ALL
SELECT '123456789A'
--===== Display the data in sorted order as requested.
SELECT SomeString
FROM dbo.TestData
ORDER BY
CAST(LEFT(SomeString,PATINDEX('%[^0-9]%',SomeString+' ')-1) AS INT),
SomeString
--Jeff Moden
Change is inevitable... Change for the better is not.
November 23, 2007 at 12:51 am
Hi everyone,
thx for help 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply