SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 String Comparision
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mahesh_bote
Constraint Violating Yak Guru

India
298 Posts

Posted - 07/24/2006 :  09:12:50  Show Profile  Reply with Quote
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

Netherlands
76 Posts

Posted - 07/24/2006 :  09:22:09  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 07/24/2006 :  09:33:03  Show Profile  Reply with Quote
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

Edited by - Kristen on 07/24/2006 10:14:56
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 07/24/2006 :  09:34:34  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
Also look in bol under String Functions

Chirag
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5564 Posts

Posted - 07/24/2006 :  09:43:01  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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 - 08/16/2006 :  22:57:19  Show Profile  Reply with Quote
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

Sweden
30250 Posts

Posted - 08/17/2006 :  01:06:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

India
22761 Posts

Posted - 08/17/2006 :  10:08:37  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Sweden
30250 Posts

Posted - 08/21/2006 :  12:43:22  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000