Sort a string in a stored procedure: not order by

Question

Sort a string in a stored procedure: not order by

chandrika5

SSCrazy

Points: 2734
More actions
July 20, 2009 at 2:41 pm

#133979

I am building an SP where I need to compare values like:
VAL1 = ABCD ; VAL2=BDAC
I need to sort both the values so that If I do a
IF VAL1 = VAL2
correct //
Functionally val1 = val2 , just that the order is not the same.
The SP does a few more things and was wondering how to incorporate such a logic in the SP.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Wesley Norton SSCrazy Points: 2827 More actions · Answer 1

Using ASCII to grab the value of the letter, the sum of the 4 character code should be the same. The problem will be if these values can have variable lengths. If that's the case then some adjustment will be necessary. Hope this helps.

declare @VAL1 char(4),@VAL2 char(4)

Set @VAL1 = 'abcd'

Set @VAL2 = 'BCDA'

select case when

ascii(upper(substring(@VAL1,1,1)))+

ascii(upper(substring(@VAL1,2,1)))+

ascii(upper(substring(@VAL1,3,1)))+

ascii(upper(substring(@VAL1,4,1)))

=

ascii(upper(substring(@VAL2,1,1)))+

ascii(upper(substring(@VAL2,2,1)))+

ascii(upper(substring(@VAL2,3,1)))+

ascii(upper(substring(@VAL2,4,1)))

then 'Equal' else 'Not Equal' end as test

edit: I just realized a downfall to this, but also realized that I needed some more info from you. Are the values all 4 characters, will they ever be shorter / longer? Are they always letters? Are they only a certain range of letters?

Mansfield SSCrazy Points: 2396 More actions · Answer 2

This is my rough code that sort each character of the string. I'm sure there are many ways that it can be improved. You can encapsulate it into a scalar function for re-use:

declare @input varchar(50)

set @input = 'GTIR'

set nocount on

declare @sort table (byte char(1), code smallint)

declare @s-2 smallint

declare @output varchar(50)

set @s-2 = 0

set @output = ''

while (@s < len(@input))

begin

set @s-2 = @s-2 + 1

insert into @sort values (substring(@input, @s-2, 1), ascii(substring(@input, @s-2, 1)))

end

--select * from @sort

select @output = @output + byte from @sort order by code

--select @output

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

This will get it done. It looks clunky, but it runs fast. You can build it into a user_defined function or CROSS APPLY it to the columns in your query. Let us know if you are unfamiliar with tally tables. I keep one in each DB, but they can also be generated on the fly.

-- CODE TO PARSE AND SORT A STRING

-- DECLARE @workTable table (element varchar(max))

DECLARE @input varchar(max)

DECLARE @output varchar(max)

SET @input = 'DBAC'

select @input as [before]

-- using tally instead of XML because it parses quicker (in my time trials anyway)

;with cte (element) as

(select substring(@input,N,1) as element

from tally

where N <= len(@input)

)

-- xml is quicker at building the output string (in my time trials)

select @output = ( SELECT '' + element

FROM cte

ORDER BY element

FOR XML PATH('')

)

select @output as [after]

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

Wesley Norton (7/20/2009)
the sum of the 4 character code should be the same.

Ummm... not necessarily...

'ABBC' = 'AABD'

--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)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

. (7/20/2009)
This will get it done. It looks clunky, but it runs fast. You can build it into a user_defined function or CROSS APPLY it to the columns in your query. Let us know if you are unfamiliar with tally tables. I keep one in each DB, but they can also be generated on the fly.
-- CODE TO PARSE AND SORT A STRING
-- DECLARE @workTable table (element varchar(max))
DECLARE @input varchar(max)
DECLARE @output varchar(max)
SET @input = 'DBAC'
select @input as [before]
-- using tally instead of XML because it parses quicker (in my time trials anyway)
;with cte (element) as
(select substring(@input,N,1) as element
from tally
where N <= len(@input)
)
-- xml is quicker at building the output string (in my time trials)
select @output = ( SELECT '' + element
FROM cte
ORDER BY element
FOR XML PATH('')
)
select @output as [after]

Oddly enough, my time trials show the same thing. 😉

--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)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 6

chandrika5 (7/20/2009)
I am building an SP where I need to compare values like:
VAL1 = ABCD ; VAL2=BDAC
I need to sort both the values so that If I do a
IF VAL1 = VAL2
correct //
Functionally val1 = val2 , just that the order is not the same.
The SP does a few more things and was wondering how to incorporate such a logic in the SP.

I'd use a method similar to the one with the Tally table, but not to do anything temporary. I would update the tables the values are in so that the values are permanently sorted instead of having to recalculate the same thing every time you wanted to do this join.

--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)

chandrika5 SSCrazy Points: 2734 More actions · Answer 7

The 4 values was just an example. The strings could be of varying length. I would not expect this to go in double digits.

I have not worked with tally tables. I will check the code out and report back.

Thanks a lot

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 8

chandrika5 (7/21/2009)
The 4 values was just an example. The strings could be of varying length. I would not expect this to go in double digits.
I have not worked with tally tables. I will check the code out and report back.
Thanks a lot

A decent introduction to what a Tally (or Numbers) table is and how it replaces a loop in a high speed fashion may be found at the following link...

http://www.sqlservercentral.com/articles/T-SQL/62867/

... it's a very powerful tool that has a large number of uses.

--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)

Wesley Norton SSCrazy Points: 2827 More actions · Answer 9

Jeff Moden (7/20/2009)
Wesley Norton (7/20/2009)
the sum of the 4 character code should be the same.
Ummm... not necessarily...
'ABBC' = 'AABD'

Thanks, Jeff. I was too hasty in my reply. Which is why I added:

edit: I just realized a downfall to this, but also realized that I needed some more info from you. Are the values all 4 characters, will they ever be shorter / longer? Are they always letters? Are they only a certain range of letters?

But then the superstars came in and saved the day! 😉

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 10

Absolutely not a problem, Wesley. I just wanted to make sure folks interpreted your good post correctly. Too many will read just one or two lines and hit the ground running. Thanks for the feedback. 🙂

--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)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 11

chandrika5,

Were you able to get it working or do you still need some help?

--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)

chandrika5 SSCrazy Points: 2734 More actions · Answer 12

Thanks Jeff. I l am learning something new: tally tables. For now, I prefer the simple ASCII add solution suggested by Weasley: I will not have ABBC type of a string. It could ABC or ABCD or something. It could go up to 10 or 12 MAX. The following :

Select @i=1

Select @retInt=0

While (@i <= Len(@str))

Begin

Select @retInt = @retInt + Ascii(Substring(@str,@i,1))

Select @i=@i+1

End

Wesley Norton SSCrazy Points: 2827 More actions · Answer 13

You did see the part about the possible duplicate sums, I hope. Just want to make sure that you have thought about the error that is inherent in my solution.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 14

chandrika5 (7/22/2009)
Thanks Jeff. I l am learning something new: tally tables. For now, I prefer the simple ASCII add solution suggested by Weasley: I will not have ABBC type of a string. It could ABC or ABCD or something. It could go up to 10 or 12 MAX. The following :
Select @i=1
Select @retInt=0
While (@i <= Len(@str))
Begin
Select @retInt = @retInt + Ascii(Substring(@str,@i,1))
Select @i=@i+1
End

How many rows are you going to need to do this on to do your join? You might want to consider accelerating your education on the Tally table and the use of XML concatenation because the While Loop is also going to be slow. See the code above from Bob (they guy with the period as a handle name) for a start.

--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)