common part of strings

nelapsi · #1 (**permalink**) 02-02-06, 06:22

hi all!
i have a table

Code:

create table t1
(
 name varchar(1000)
);

how can I find common part of strings in this table?
example:

Code:

select * from t1;
'asddddddddd'
'asdf'
'asdfgh'
'asdcvf'

common part will be 'asd'

Wim · #2 (**permalink**) 02-02-06, 10:07

Code:

select * 
from t1
where name like 'asd%';

nelapsi · #3 (**permalink**) 02-03-06, 02:34

Quote:

Originally Posted by Wim

Code:

select * 
from t1
where name like 'asd%';

I don't know that strings in table and I want to find common part

urquel · #4 (**permalink**) 02-03-06, 08:57

According to your logic, there would be several "common parts".

a, as, asd, s, sd, d

Maybe a more specific definition of common parts would make a solution more determinate.

Peter.Vanroose · #5 (**permalink**) 02-05-06, 09:59

Anyway, there is no standard column function which does what you want.
You may implement such a function, though. It would probably look something like the following pseudo-code:

Code:

value = (SELECT MAX(name) FROM t1);
nonmatches = 0;
length = 0;
while (nonmatches = 0)
   length = length+1;
   prefix = SUBSTR(value,1,length);
   nonmatches = (SELECT COUNT(*) FROM t1 WHERE SUBSTR(name,1,length) <> prefix)
endwhile;
return SUBSTR(value,1,length-1);

gardenman · #6 (**permalink**) 02-06-06, 08:59

Very interesting task.
I think you have to write yours own C/C++ function and keep your result for each time.
I wrote similar function called KEEP.
Here is the link:

http://www.sql****/forum/actualthread...244580&hl=keep

Internet Services

Web Development

Digital Marketing

Consumer Tech