SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Select Smart query


Select Smart query

Author
Message
narezzi
narezzi
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 2
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
Animal Magic
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1630 Visits: 13731
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
LutzM
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10527 Visits: 13559
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search