Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2004
    Posts
    43

    Unanswered: common part of strings

    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'

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    select * 
    from t1
    where name like 'asd%';
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Dec 2004
    Posts
    43
    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

  4. #4
    Join Date
    Aug 2004
    Posts
    330
    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.

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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);
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Apr 2004
    Posts
    54
    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.ru/forum/actualthread...244580&hl=keep

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •