SitePoint Sponsor

User Tag List

Results 1 to 11 of 11

Hybrid View

  1. #1
    SitePoint Wizard Goof's Avatar
    Join Date
    Feb 2001
    Location
    Pittsburgh, PA
    Posts
    1,154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Combining two fields in MSSQL for search.

    Hello,

    I'm working on a project where I want to search multile fields in a table based on user input. Is there a way to combine these fields in my SELECT statement (with an AS clause) so I can only do a WHERE clause on one field?

    For instance, if I have a firstName and lastName field and I want to search by full name, I want to do something like:
    Code:
    SELECT (firstName & ' ' & lastName) as fullName WHERE fullName LIKE '%lee%';
    ...and have it pull both "Lee Jones" and "Bruce Lee".

    I know that I could search both fields using an AND joiner, but I'm talking about 10 fields or so, and I'd like to just be able to join them into one search field.

    Any ideas?

    Thanks,
    Goof
    Nathan Rutman
    A slightly offbeat creative.

  2. #2
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So close, Goof!

    This works for Access:
    Code:
    SELECT ([firstName] & " " & [lastName]) as fullName FROM Contacts WHERE ([firstName] & " " & [lastName])  LIKE '*lee*';
    (You may well need to change those *s to something else for SQL Server)


    M@rco
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  3. #3
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT (ISNULL([firstName], '') + " " + ISNULL([lastName], '')) as fullName FROM Contacts WHERE (ISNULL([firstName], '') + " " + ISNULL([lastName], '')) LIKE '%lee%';

    ..handles any case where the fields may allow NULLs. (because string + NULL = NULL)
    -- JIM BOLLA
    Wanna play Halo 2? My XBOX Live gamertag: crowdozer

  4. #4
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Remember that will table scan.

  5. #5
    SitePoint Wizard Goof's Avatar
    Join Date
    Feb 2001
    Location
    Pittsburgh, PA
    Posts
    1,154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    m@rco and crowdozer: thanks!

    mattr: table scan?

    Thanks,
    Goof
    Nathan Rutman
    A slightly offbeat creative.

  6. #6
    SitePoint Wizard Goof's Avatar
    Join Date
    Feb 2001
    Location
    Pittsburgh, PA
    Posts
    1,154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    crowdozer, when I use an AS clause, can I not use the renamed field in the where clause? Meaning, can't I do:
    Code:
    SELECT (isNull([firstName], '') + isNull([lastName], '')) as FullName WHERE FullName LIKE '%somtext%';
    Thanks,
    Goof
    Nathan Rutman
    A slightly offbeat creative.

  7. #7
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    unfortunately not. it would be a great feature if we could. i think the reason its not already in there is probably because it could lead to naming conflicts, such as the case:

    SELECT field1 as field2, field2 as field3 from table where field2 = 'blah'

    ...in the where clause, which value should "field2" refer to? the alias field2 or the actual field2?

  8. #8
    SitePoint Wizard Goof's Avatar
    Join Date
    Feb 2001
    Location
    Pittsburgh, PA
    Posts
    1,154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    crowdozer, thanks for the information.

    In my opinion, "field2" should refer to [field1] where [field2] should refer to [field2]...at least that makes sense to me. Otherwise, it could always give us a name conflict error.

    *shrug*
    Goof
    Nathan Rutman
    A slightly offbeat creative.

  9. #9
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    AFAIK it's because the SQL query is really in two parts - it does the query on ALL the data (the WHERE part), and then returns the data that you want (the SELECT part).

    Thus you can't use a field you have created in the SELECT part in the WHERE part, since it is not actually created until after the data has been queried!


    M@rco
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  10. #10
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    good explination, m@rco. that makes a lot of sense. i can't believe i didn't think of it that way.

  11. #11
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Goof
    mattr: table scan?
    That means your SQL engine will read each and every row in an effort to find a match and will be very slow. It is explained more in MS SQL documentation for LIKE clauses.


Bookmarks

Posting Permissions

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