Select Smart query

• narezzi

  SSC Enthusiast

  Points: 199

  More actions

  July 20, 2010 at 12:42 am

  #141224

  Hello All,

  I looking for a very cleaver query

  I have a field LastName and by users mystake there are some that is having to names over that

  for E.g//

  FisrtName Lastname

  Rafael Narezzi

  That is ok

  My problem is that I want a select to search for all users that is like that

  Fisrtname LastName

  Rafael Rafael Narezzi

  how could I do a select to get all users that have spaces between?

  Thanks

• Animal Magic

  SSChampion

  Points: 13964

  More actions

  July 20, 2010 at 2:52 am

  #1195362

  you could do a charindex check for a blank space and make sure the number returned is greater than 0. Make sure you trim your input though incase you have white space.

  I think you could also use like '% %' but that should probably be avoided for performance reasons

  i would personally use charindex but would like to see what others say

• LutzM

  SSC Guru

  Points: 107049

  More actions

  July 20, 2010 at 12:30 pm

  #1195735

  Here are two options you could use:

  --check for first name used as the first part of the last name

  SELECT *

  FROM @tbl

  WHERE LastName LIKE RTRIM(Fisrtname)+' %'

  -- check for a space in LastName (might produce false results, e.g. for Pietro de la Rosa ;-))

  SELECT *

  FROM @tbl

  WHERE CHARINDEX(' ',LastName)>0

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]