Sorting a field
-
I have table in which the accountnumber will contain both numeric and character data. When displaying in my grid, i need to check if it is a numeric data i have to sort it numerically and ignore if it is character data.
Kindly help
Kindest Regards,sree
-
I was VERY errant in not noting who wrote this to give proper credit! I got this off a posting on this site. If you wrote it, please respond so I can update my script.
Here is a possible solution. It sorts on Numeric first, then by Alpha.
CREATE FUNCTION [dbo].[nmbPadd]( @mystr varchar(20))
RETURNS varchar(4000)
AS
BEGIN
DECLARE @StartNmb integer
DECLARE @StopNmb integer
DECLARE @pos integer
DECLARE @isNumb bit
DECLARE @myLen integer
DECLARE @rez varchar(4000)
IF( @myStr IS NULL OR LEN( @myStr) = 0) RETURN ''
SET @rez = ''
SET @myLen = LEN( @mystr)
SET @isNumb = 0
SET @pos = 1
WHILE( @pos <= @myLen)
BEGIN
IF( CHARINDEX( SUBSTRING( @mystr, @pos, 1), '0123456789') > 0)
BEGIN
--check to see if the prev char was numeric
IF( @isNumb = 1)
BEGIN
SET @StopNmb = @StopNmb + 1
END
ELSE
BEGIN -- prev char was NOT numeric
SET @StartNmb = @pos
SET @StopNmb = 1
END
SET @isNumb = 1
END
ELSE -- our current char is not numeric
BEGIN
-- check to see if the prev char was numeric
IF( @isNumb = 1)
BEGIN
-- 10 is max number of digitis that a number can have
SET @rez = @rez + REPLICATE( '0', 10 - @StopNmb) + SUBSTRING( @myStr, @StartNmb, @StopNmb + 1)
END
ELSE
BEGIN -- prev char was NOT numeric
SET @rez = @rez + SUBSTRING( @mystr, @pos, 1)
END
SET @isNumb = 0
END
SET @pos = @pos + 1
END
IF( @isNumb = 1)
SET @rez = @rez + REPLICATE( '0', 10 - @StopNmb) + SUBSTRING( @myStr, @StartNmb, @StopNmb)
RETURN @rez
END
GO
DECLARE @t TABLE( sort varchar(20))
INSERT INTO @t
SELECT '1dd' UNION ALL
SELECT '1' UNION ALL
SELECT '1x4' UNION ALL
SELECT '1cc2' UNION ALL
SELECT '1110-345-720a3' UNION ALL
SELECT '11' UNION ALL
SELECT '380-41-3a' UNION ALL
SELECT '10'
SELECT sort FROM @t
ORDER BY [dbo].[nmbPadd](sort)
DROP FUNCTION [dbo].[nmbPadd]
I wasn't born stupid - I had to study.
-
Here's a good thread to reference....
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=328228
-
Way better! Or, is that way more better?
Thanks John!
I wasn't born stupid - I had to study.
-
Hmmm...
My script returns:
sort
--------------------
1
1cc2
1dd
1x4
10
11
380-41-3a
1110-345-720a3
The Robert Davis script returns:
sort
--------------------
1x4
1cc2
1110-345-720a3
1dd
380-41-3a
1
10
11
I think the latter is not as good...
I wasn't born stupid - I had to study.
-
Sree,
Rather than us guess, do you have some samples of the data before and after the sort you'd like?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
This could be another way, dont know how much performance you gonna get out of it.
Here's the data in table1
data
1dd
1
1x4
1cc2
1110-345-720a3
11
380-41-3a
10
12983
21
19
--------------------------------
SELECT data,isnumeric(data)
FROM dbo.table1
ORDER BY CASE WHEN ISNUMERIC(data) = 1 THEN CAST(data AS INT) END ASC,
CASE WHEN ISNUMERIC(data) = 0 THEN data END ASC
Result:
data isnumeric(data)
1110-345-720a3 0
1cc2 0
1dd 0
1x4 0
380-41-3a 0
1 1
10 1
11 1
19 1
21 1
12983 1
Thanks,
Dinesh.
-
Dinesh, replace "1dd" with "1d3" int your data set and rerun your query.
See what happens.
_____________
Code for TallyGenerator -
Do you mean like this when you say to "ignore if is character data"???
DECLARE @t TABLE( sort varchar(20))
INSERT INTO @t
SELECT '1dd' UNION ALL
SELECT '1x4' UNION ALL
SELECT '1d3' UNION ALL
SELECT '1cc2' UNION ALL
SELECT '100' UNION ALL
SELECT '1110-345-720a3' UNION ALL
SELECT '11' UNION ALL
SELECT '380-41-3a' UNION ALL
SELECT '1' UNION ALL
SELECT '10'
SELECT *
FROM @t
WHERE Sort NOT LIKE '%[^0-9]%'
ORDER BY CAST(Sort AS INT)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Sergiy, that was a good catch but there are very few instances where we get that kind of a sequence where d --> decimal should be considered as a string. Still its a bug for the above script and this is how i fixed it.
This function seperates numbers from strings
---------------------------------------------------------------
CREATE FUNCTION dbo.fn_seperate_num_alpha(@string varchar(100))
RETURNS bit
AS
BEGIN
DECLARE @position int,@OUT INT,@OUTP BIT
-- Initialize the current position and the string variables.
SET @position = 1
SET @OUTP = 1
WHILE @POSITION <= LEN(@STRING)
BEGIN
-- Check if each letter in the string is number or not
IF (ASCII(SUBSTRING(@string, @position, 1))) BETWEEN 48 AND 57
SET @OUT = 1
ELSE
SET @OUT = 0
SET @position = @position + 1
SET @OUTP = @OUTP & @OUT
END
-- returns 1 if it is number, returns 0 if there is any characters apart from numbers
RETURN @OUTP
END
--------------------------------------------------------------------------------
This query returns the result set
--------------------------------------------------------------------------------
SELECT data,dbo.fn_seperate_num_alpha(data)
FROM dbo.t1
ORDER BY CASE WHEN dbo.fn_seperate_num_alpha(data) = 1 THEN CAST(data AS INT) END ASC,
CASE WHEN dbo.fn_seperate_num_alpha(data) = 0 THEN data END ASC
--------------------------------------------------------------------------------
-
Dinesh,
Jeff posted more clear and effective solution just 1 post above.
Actually he has to post this solution twice a week, because people don't bother to read previous postings.
CASE WHEN Data NOT LIKE '%[^0-9]%' THEN CAST(data AS INT) END
No UDF required.
_____________
Code for TallyGenerator -
Heh...
Dinesh... take a look at the following for a complete explanation as to why ISNUMERIC should never be treated as ISALLDIGITS... and, it's not a fault... it's a feature
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=243646&p=2
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Hai Jeff,
The data in that field would be like
0000022211
0000022243
0000072211
0000102221
AB00011
CD000111
The kind of output i would like to have is:
22211
22243
72211
102221
AB00011
CD000111
The field has '0' in front to convert it to 10 digits . If it is a numeric one it has to trim zero's just display the numeric values and sort them numerically and if it has character it has to display as it is
. I need a query that would return this o/p as i have to use this in a view . If i use the query tht dinesh asked to use it would return like102221
22211
22243
72211
AB00011
CD000111 which i dont want. Hope i'm more clear now.
Kindest Regards,sree
-
Perfectly clear, thank you...
DECLARE @TestData TABLE (StringValue VARCHAR(20))
INSERT INTO @TestData
SELECT '0000022211' UNION ALL
SELECT '0000022243' UNION ALL
SELECT '0000072211' UNION ALL
SELECT '0000102221' UNION ALL
SELECT 'AB00011' UNION ALL
SELECT 'CD000111'
SELECT CASE
WHEN StringValue LIKE '[^0-9]%'
THEN StringValue
ELSE CAST(CAST(StringValue AS INT) AS VARCHAR(20))
END AS SortedString
FROM @TestData
ORDER BY CASE
WHEN StringValue LIKE '[^0-9]%'
THEN StringValue
ELSE STR(CAST(StringValue AS INT),20)
END
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Hi Jeff,
Thanks a lot!! . I got it working
Kindest Regards,sree
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply