CODEWITHSUNDEEP
sqlstringsql-server-2008
Sahi
Sahi

Reputation: 1484

Strings with space in between

I need a query to get string with space in between.

i.e it should not return strings like

' abc', 'abc ' and ' abc '

and it should return strings like 

'ab c' ,' ab c', 'ab c ' and ' ab c '

i tried with below query.

select user_fname,user_lname
from user where user_fname like '% %';

but it is returning all the rows.

Upvotes: 0

Views: 93

Answers (3)

Neeraj Kumar Singh
Neeraj Kumar Singh

Reputation: 51

you could try this also

select user_fname,user_lname
from user lTrim(rTrim(user_fname)) LIKE '% %'

this will remove the white space left and right of user_fname and then it will try to find the white space between the character.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

% matches zero or more characters. I'd suggest adding some _s in:

select user_fname,user_lname
from user where user_fname like '%_ _%';

If that's still matching too much, perhaps:

select user_fname,user_lname
from user where user_fname like '%[^ ] [^ ]%';

Which will match zero or more characters, then something that definitely isn't a space, a space, something that definitely isn't a space and then zero or more characters.

Upvotes: 3

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try this:

SELECT user_fname,user_lname
FROM user 
WHERE user_fname LIKE '% %' AND user_fname NOT LIKE ' %' AND user_fname NOT LIKE '% '

Upvotes: 2

Related Questions