Comparing strings - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Comparing strings

Author

Topic

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-09-19 : 11:42:23

Is there a funcion in sql that will compare text strings no matter of the order of the characters in the string.

'ABC' <> 'XYZ'
but
'ABC' does equal 'BCA' since the letters in each are all the same.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-19 : 12:34:16

No, you'd have to write your own.

There is DIFFERENCE function which returns a value based on the results of SOUNDEX from both strings. You can read about them in Books Online and try it to see if it will do what you want.

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-20 : 03:23:23

One of the methods is

declare @data1 varchar(10), @data2 varchar(10)
select @data1='abbc', @data2='bbca'

declare @string1 varchar(10), @string2 varchar(10)
select @string1 ='', @string2 =''

select @string1 =@string1 +data from
(
select top 100 percent substring(@data1,number,1) as data--,@string2 =@string2+substring(@data2,number,1)
from master..spt_values as m where m.type='p' and number<=len(@data1) and number>0
order by substring(@data1,number,1)
) as t

select @string2 =@string2 +data from
(
select top 100 percent substring(@data2,number,1) as data--,@string2 =@string2+substring(@data2,number,1)
from master..spt_values as m where m.type='p' and number<=len(@data1) and number>0
order by substring(@data2,number,1)
) as t

select case when @string1 =@string2 then 'equal' else 'unequal' end

Thanks Tan for finding the error

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-20 : 06:08:06

That is not full proof.
select @data1='ac', @data2='bb' will give you equal also

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-20 : 06:37:45

quote:
Originally posted by khtan

That is not full proof.
select @data1='ac', @data2='bb' will give you equal also

KH
[spoiler]Time is always against us[/spoiler]

I corrected it

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-20 : 06:46:20

it's still returning equal

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-20 : 06:54:27

quote:
Originally posted by khtan

it's still returning equal

KH
[spoiler]Time is always against us[/spoiler]

Can you post the data you used?

declare @data1 varchar(10), @data2 varchar(10)
select @data1='ac', @data2='bb'

declare @string1 varchar(10), @string2 varchar(10)
select @string1 ='', @string2 =''

select @string1 =@string1 +data from
(
select top 100 percent substring(@data1,number,1) as data--,@string2 =@string2+substring(@data2,number,1)
from master..spt_values as m where m.type='p' and number<=len(@data1) and number>0
order by substring(@data1,number,1)
) as t

select @string2 =@string2 +data from
(
select top 100 percent substring(@data2,number,1) as data--,@string2 =@string2+substring(@data2,number,1)
from master..spt_values as m where m.type='p' and number<=len(@data1) and number>0
order by substring(@data2,number,1)
) as t

select case when @string1 =@string2 then 'equal' else 'unequal' end

Madhivanan

Failing to plan is Planning to fail

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-09-20 : 07:17:31

You could have a function that sorts the string's characters.

No guarantees this one works, but it might be worth a try!
Note that it doesn't treat trailing spaces as significant, and it's not very fast.


CREATE FUNCTION dbo.SortString (@s nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
  IF @s = N'' RETURN N''

  DECLARE @r nvarchar(max)
  SET @r = N''
  ;WITH Chars(n, c) AS (
    SELECT 1, SUBSTRING(@s, 1, 1) COLLATE Latin1_General_BIN2
    UNION ALL
    SELECT n + 1, SUBSTRING(@s, n + 1, 1) COLLATE Latin1_General_BIN2
    FROM Chars
    WHERE n < LEN(@s)
  )
  SELECT @r = @r + c
  FROM Chars
  ORDER BY c
  OPTION (MAXRECURSION 0)
  RETURN @r
END

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-09-20 : 09:44:34

try this:


DECLARE @str1 VARCHAR(100), @str2 VARCHAR(100)
SELECT @str1 = 'abc', @str2 = 'bca'
--SELECT @str1 = 'a7c ', @str2 = 'aa bc'

IF LEN(@str1) <> LEN(@str2)
BEGIN
	SELECT 'NOT EQUAL'
END 
ELSE
BEGIN
	IF (SELECT COUNT(*) FROM (
							select	substring(@str1, number, 1) as data
							from	master..spt_values as m 
							where	m.type='p' and number <= len(@str1) AND number > 0
							UNION
							select	substring(@str2, number, 1) as data
							from	master..spt_values as m 
							where	m.type='p' and number <= len(@str2) AND number > 0
						) t)
		= LEN(@str1)
		SELECT 'EQUAL'
	ELSE
		SELECT 'NOT EQUAL'
END

EDIT: Posted the correct version

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-21 : 02:56:18

quote:
Originally posted by madhivanan

quote:
Originally posted by khtan

it's still returning equal

KH
[spoiler]Time is always against us[/spoiler]

Can you post the data you used?

declare @data1 varchar(10), @data2 varchar(10)
select @data1='ac', @data2='bb'

declare @string1 varchar(10), @string2 varchar(10)
select @string1 ='', @string2 =''

select @string1 =@string1 +data from
(
select top 100 percent substring(@data1,number,1) as data--,@string2 =@string2+substring(@data2,number,1)
from master..spt_values as m where m.type='p' and number<=len(@data1) and number>0
order by substring(@data1,number,1)
) as t

select @string2 =@string2 +data from
(
select top 100 percent substring(@data2,number,1) as data--,@string2 =@string2+substring(@data2,number,1)
from master..spt_values as m where m.type='p' and number<=len(@data1) and number>0
order by substring(@data2,number,1)
) as t

select case when @string1 =@string2 then 'equal' else 'unequal' end

Madhivanan

Failing to plan is Planning to fail

Sorry it's ok now.

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-22 : 01:46:23

quote:

Originally posted by spirit1

try this:


DECLARE @str1 VARCHAR(100), @str2 VARCHAR(100)
SELECT @str1 = 'abc', @str2 = 'bca'
--SELECT @str1 = 'a7c ', @str2 = 'aa bc'

IF LEN(@str1) <> LEN(@str2)
BEGIN
	SELECT 'NOT EQUAL'
END 
ELSE
BEGIN
	IF (SELECT COUNT(*) FROM (
							select	substring(@str1, number, 1) as data
							from	master..spt_values as m 
							where	m.type='p' and number <= len(@str1) AND number > 0
							UNION
							select	substring(@str2, number, 1) as data
							from	master..spt_values as m 
							where	m.type='p' and number <= len(@str2) AND number > 0
						) t)
		= LEN(@str1)
		SELECT 'EQUAL'
	ELSE
		SELECT 'NOT EQUAL'
END

It fails with the data such as

abc, aac
aab abc
etc

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-09-22 : 04:48:38

bummer

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-09-22 : 11:20:54

If this were simply, or mostly, alphabetic characters, you could store along with the string a bitmap representing the letters it contained.
Then, finding strings with matching letter sets would be easy.
Bigint bitmaps can handle 32 values, which is enough to handle letters and some punctuation, but would not handle numeric characters.

Boycotted Beijing Olympics 2008

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-22 : 11:33:05

BIGINT is 64 bit, right?

E 12°55'05.63"
N 56°04'39.26"

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-09-22 : 12:44:32

Oh yeah. You are correct. So a BigInt bitmask might handle the characters he would need, especially if case sensitivity was not an issue.

Boycotted Beijing Olympics 2008

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-23 : 07:24:11

Or uniqueidentifier which is 128 bit?

E 12°55'05.63"
N 56°04'39.26"

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-09-23 : 09:05:01

To quote SQL Server, "Operand data type uniqueidentifier is invalid for boolean AND operator." So, like varbinary, it would be a bit of a pain to use uniqueidentifier for a bitmask.

But this is all speculation. The original question didn't specify if comparison was supposed to be on a set of characters or on a bag (multiset). For all we know, they might have wanted to find all the anagrams in their column names!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-09-23 : 10:37:33

quote:
Originally posted by Peso

Or uniqueidentifier which is 128 bit?

Interesting. How would you use a UID as a bitmask, though? I wouldn't think you could use the standard bitwise operators on it.

Boycotted Beijing Olympics 2008

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-23 : 10:59:49

quote:
Originally posted by Van

Is there a funcion in sql that will compare text strings no matter of the order of the characters in the string.

'ABC' <> 'XYZ'
but
'ABC' does equal 'BCA' since the letters in each are all the same.

this is a fun thread, but I have to wonder if the best "solution" is probably a database redesign. Sounds like a CSV column is being used when it should be normalized.

Van -- why do you need this function? Are you stuffing multiple values into one big column, instead of breaking them out into a single column in multiple rows?

- Jeff
http://weblogs.sqlteam.com/JeffS

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-10-20 : 17:25:56

Sorry to take so long to answer. I didn't realize this thread had any more posts. I figured out a solution.

Bascially the problem was this. An item has multiple records in a table and there's a modifed_type field. So let's say an item has 4 records and the modified_types are (a,b,d,f). I need to use these values to update another field (for all four records). I was given a table (via email) of about 40 values or so to be used to update based on all the different possibilities for modified_type. I also had to make this data I got in the email into a table to be used for new 'patterns' that may be needed for the future. So there is one record in this table that corresponds to 'abdf' and tells me what value to use for the update. So I wrote a loop to go through all the records for the item (4 in this case) and concatinate a string. I got a string of 'abdf' in this case, but there's no guarantee that I'd get it in alphabetical order unless I sorted. And there's no guarantee the lookup table would be in alphabetical order like 'abdf' going forward. They may add to it and put in 'wafg' which would correspond to 'afwg', 'awgf',...and so on.

So I was wondering if there was an easier way to take a string (the one I concatenated) and check if all of the characters in that string existed in a field or not. (abc = bac = bca = acb = cba = cab)

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-20 : 18:36:38

Then Arnold Fribble's post about SortString should work perfectly.

BCA = ABC
but
BCAA <> ABC

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources