Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    5

    Unanswered: SQL Query for Large strings into small strings

    HI

    As a relative novice to SQL I have an interesting problem which I am trying to resolve;

    What I need to do, I am sure is obvious, is find all the smaller strings held in a table that can match a larger string from the left

    e.g

    Table holds
    "001"
    "001345678"
    "00158"
    "001582"
    "001978"

    I then have a string "0015897654" or some such and I need the results to be

    "001"
    "00158"

    I dont want to have to do reiterative comparisons

    Any Ideas

    SB

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: SQL Query for Large strings into small strings

    Try:

    select code
    from table
    where '0015897654' like concat(code,'%');

  3. #3
    Join Date
    Mar 2004
    Posts
    5

    Re: SQL Query for Large strings into small strings

    I am using an access table at the moment to hold the data and access doesn't support concat in its SQL support

    Have tried code + "%" but that doesnt work

    Any other ides?

    All gratefully excepted

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    SELECT LEFT(yourField, 5)

  5. #5
    Join Date
    Mar 2004
    Posts
    5
    Sorry

    I'm obviously very dense today

    I need to find the entries in the table that are a SUBSET of the LONGER string.

    e.g if the String is "0015089456"
    if my table holds
    "001"
    "001234"
    "0015"
    "0015134"
    "001508"
    "00151897654"

    I would expect the result set to be
    "001"
    "0015"
    "001508"

    Its not quite as simple as it first looks

    Thanks

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    crazy idea - haven't checked if it squeezes into a query or not:

    SELECT * FROM blah WHERE left$(" & stringVarOrTextbox & ", len(rtrim$([yourField])) = rtrim$([yourfield])

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Mar 2004
    Posts
    5
    Yep

    That works

    Now I want to have only the LONGEST string that matches

    Any ideas?


    Thanks

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    SELECT TOP 1 FROM blah WHERE left$(" & stringVarOrTextbox & ", len(rtrim$([yourField])) = rtrim$([yourfield]) ORDER BY len(rtrim$([yourField])) DESC

    ???


    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Mar 2004
    Posts
    5
    Much appreciated

    Works like a dream

Posting Permissions

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