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 2000 Forums
 SQL Server Development (2000)
 String Comparision

Author  Topic 

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-07-24 : 09:12:50
Can anybody tell me, how to compare two strings in SQL? Is there any StrComp like function in SQL Server?

Thanks in Advance,

Mahesh

Q
Yak Posting Veteran

76 Posts

Posted - 2006-07-24 : 09:22:09
Could you tell us more? Some table information (columns) and what do you want to do with the comparison?

* where string1=string2 ???
* DIFFERENCE(string1, string2) ???
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-07-24 : 09:33:03
Note that

where string1=string2

will be case INsensitive if your database is set up to NOT be case-sensitive. (You can force the collation if you want a case SENSITIVE comparison in a non-case-sensitive environment).

You can also do:

where string1 LIKE string2

and "string2" can include the wildcard operators:

% (0 or more characters)
_ any single character (i.e. will NOT match an empty string)
[ABC0123] a single character, one of the set "ABC0123"
[0-9] a single digit
[^0-9] a single character which is not a digit

Further Regular Expressions are not currently supported - so you can't do:
[0-9]+[A-Za-z]+
for one or more digits followed by one or more letters

You can also use an ESCAPE character if you need to escape one of the reserved characters:

WHERE MyColumn LIKE '%\_FOO\_%' ESCAPE '\'

In a comparison if one of the objects is NULL then the result will be false. BOTH of these will return no rows:

SELECT 'no rows'
WHERE NULL = 0

SELECT 'no rows'
WHERE NULL <> 0

Kristen
Go to Top of Page

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-24 : 09:34:34
Also look in bol under String Functions

Chirag
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-24 : 09:43:01
quote:
Originally posted by Kristen

Note that

where string1=string2

will be case INsensitive if your database is set up to NOT be case-sensitive. (You can force the collation if you want a case SENSITIVE comparison in a non-case-sensitive environment).

You can also do:

where string1 LIKE string2

and "string2" can include the wildcard operators:

% (0 or more characters)
_ and single character
[ABC0123] a single character, one of the set "ABC0123"
[0-9] a single digit
[^0-9] a single character which is not a digit

You can also use an ESCAPE character if you need to escape one of the reserved characters:

WHERE MyColumn LIKE '%\_FOO\_%' ESCAPE '\'

Kristen




Cool man,

That was pretty good coverage of the subject !!


Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

joseln
Starting Member

11 Posts

Posted - 2006-08-16 : 22:57:19
Hi all,

i have a field value A,B,A,C,D,B

i want to concatenate when i run the above values in loop but i dont want to concatenate duplicates, meaning, when the loop runs 3rd time as "A" is already there in the first position it has to skip it and goes to the next record ie "C" .

i will be able to resolve this by written a seperate function or procedure, but is there any way in SQL Server link "StrComp" of VB.

thanks
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 01:06:38
If the values are in one column and several rows, use something like following
declare @test table ([option] varchar(2))

insert @test
select 'A' union all
select 'B' union all
select 'A' union all
select 'C' union all
select 'D' union all
select 'B'

declare @concat varchar(8000)

select @concat = isnull(@concat + ',', '') + z.[option]
from (
select distinct top 100 percent [option]
from @test
order by [option]
) z

select @concat as Result
If you have one row and all values are in one column, start with
declare @test table ([option] varchar(2))

insert @test
select distinct param
from fnSplit('A,B,A,C,D,B')

declare @concat varchar(8000)

select @concat = isnull(@concat + ',', '') + z.[option]
from (
select top 100 percent [option]
from @test
order by [option]
) z

select @concat as Result


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-17 : 10:08:37
When you apply Distinct, you dont need Order by. Is it?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 12:43:22
Most often no. But I have come into situations where distinct didn't return the values sorted.
I think that is to which indexes that are in use.

Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

- Advertisement -