Morning cup o java - 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

Site Related Forums

The Yak Corral

Morning cup o java

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2005-01-04 : 08:24:14

Two strings
@str1 VARCHAR(100)
@str2 VARCHAR(100)

What is the neatest, fastest, most elegant solution that will return the the leftmost characters which match both strings ala T-SQL?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-01-04 : 08:34:12

Probably a UDF that takes two string arguments and compares them character by character until it finds a mismatch, then returns the matching. Sounds like it would be slow, but it would completely avoid I/O operations.

I'm pretty sure there's a way to do it using a tally/sequence/numbers table, but I doubt it would be neat or elegant. Of course that's never stopped me before.

If I get bored at work today I'll give it a try.

SamC
White Water Yakist

3467 Posts

Posted - 2005-01-04 : 08:40:39

Slow slow slow

BTW - this is completely hypothetical, so don't break a leg... I'd call this a challenge but it's really not tough to find a solution. I thought it would be worth a post because there may be an elegant solution, which doesn't require a UDF and probably 2 to 3 lines of SQL tops (which will probably be posted by Arnold when he returns from lunch).

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-01-04 : 09:00:19

Does this work?


Declare @str1 varchar(100),
	@str2 varchar(100)

Set @str1 = 'the quick brown fox jumped over the lazy dog.'
Set @str2 = 'the quick brown foxes jumped over the lazy dogs.'

Select @str1, @str2

Select top 1 Str1 = left(@str1,number-1), Str2 = left(@str2,number-1)
from admin.dbo.getSequence(0,1000,1) A
Where substring(@str1,number,1) <> substring(@str2,number,1)

Corey

SamC
White Water Yakist

3467 Posts

Posted - 2005-01-04 : 09:26:34

Worked for me as:

Declare @str1 varchar(100),
	@str2 varchar(100)

Set @str1 = 'the quick brown fox jumped over the lazy dog.'
Set @str2 = 'the quick brown foxes jumped over the lazy dogs.'

Select @str1, @str2

Select top 1 Str1 = left(@str1,ID-1), Str2 = left(@str2,ID-1)
from dbo.Tally A -- I keep this tally table lying around for jobs like this
Where substring(@str1,ID,1) <> substring(@str2,ID,1)

I was wondering if there were any more efficent solution that involved exclusive oring the two strings...

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-01-04 : 09:41:00

quote:
which will probably be posted by Arnold when he returns from lunch).

Sorry to disappoint, but I'm with the others on this: scan the two strings together character-by-character until you hit a mismatch. Either implemented as a WHILE loop in a UDF or with a tally table in a SELECT.

If you want to compare one string with a whole column-full of strings to find the ones with the longest matching prefix, that could get more interesting.

(Though not much!)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-04 : 13:17:21

Another Tally solution...

Declare @str1 varchar(100),
	@str2 varchar(100)

Set @str1 = 'the quick brown fox jumped over the lazy dog.'
Set @str2 = 'the quick brown foxes jumped over the lazy dogs.'

select max(substring(@str1,1,number))
from
	master.dbo.spt_values tally
where
	patindex(substring(@str1,1,number)+'%',@str2) = 1
	and tally.type = 'P'

rockmoose

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-04 : 13:29:42

If you want the fastest method, that would be searching character by character using a UDF, w/o a tally table. Not as cool as with a tally table, of course!

- Jeff

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-04 : 15:41:34

Even with very long strings and binary (or other) algorithms the char by char method would be fastest I guess.
Unless there is some really snazzy strcmp function when @strx = @stry is evaluated.

But I am really unsure about the overhead of calling an UDF over&over.
Deep down, how do the UDFs gewt compiled and called anyay?

rockmoose

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-01-05 : 11:21:26

Would this be easier in BASE 26 (or some other base where all the alphabet can be included)?

steve

And how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-06 : 07:40:39

SamC;

I still prefer chocolate rather than java (what is it? never drunk it).
To be fair, only those guys from the top ~10 of ranklist on my link I count for
real programmers (yet most of them are just students).
Or maybe it is worth discussing who the programmers are in our days?

PS Never mind! I'm a light year behind the top 10. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources