Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Regarding sql server table column search with PATINDEX

Answered Regarding sql server table column search with PATINDEX

  • Thursday, March 07, 2013 7:54 AM
     
     
    Sign In to Vote
    0
    i have never use PATINDEX() but i hard the table data can be search with PATINDEX(). often i got requirement to search multiple column of any table then i write the sql like

    SELECT * FROM ADDRESS WHERE
    ((NAME LIKE 'Bill%') OR (CITY LIKE 'Bill%') OR (COMPANY LIKE 'Bill%'))
    AND
    ((NAME LIKE 'Seattle%') OR (CITY LIKE 'Seattle%') OR (COMPANY LIKE 'Seattle%'))

    so just tell me the above my sql performance will be good always? i search google to get better sql for searching multiple column of a table and found the below sql

    select * from YourTable
    WHERE PATINDEX('%text1%',COALESCE(field1,'') + '|' + COALESCE(field2,'') + '|'+ COALESCE(field3,'')+ '|' + COALESCE(field4,'')+ '|' + COALESCE(field9,''))>0
    AND
     PATINDEX('%text2%',COALESCE(field1,'') + '|' + COALESCE(field2,'') + '|'+ COALESCE(field3,'')+ '|' + COALESCE(field4,'')+ '|' +COALESCE(field9,''))>0

    please guide me that the above sql PATINDEX syntax is ok for searching multiple column. if not then guide me how can i use the PATINDEX function to search multiple column with multiple value. thanks
     

All Replies

  • Thursday, March 07, 2013 8:31 AM
     
     
    Sign In to Vote
    0
    Probably first one looks better. Also your second query is wrong I guess because PATINDEX returns an INT value which will force | to get converted into INT 

    Thanks and regards, Rishabh K

     
  • Thursday, March 07, 2013 10:07 AM
     
     
    Sign In to Vote
    0
    can u please tell me how could i use PATINDEX function to search multiple column of table??
     
  • Thursday, March 07, 2013 11:31 AM
     
     Answered
    Sign In to Vote
    0

    The PATINDEX similar to CHARINDEX function.The PATIDEX function returns the integer value.

    It returns the statring position of the pattern in the speciified string.

    Ex: Select PATINDEX('%jack', 'kimberly jackson')

    The output is :10 (ie the position of the string "jack"  in "Kimberly jackson".

    Not recommend to use the PATINDEX in where clause. Since its not using the Indexes if available

    • Marked As Answer by Mou_kolkata Thursday, March 07, 2013 1:18 PM
    •