CODEWITHSUNDEEP
sqlsql-servert-sql
Andy Williams
Andy Williams

Reputation: 907

SQL Query: Better Option for Returns First and Last Names with Spaces?

I am trying to find a list of Customers who's FirstName OR LastName Contains an Empty space in the middle of the name. Here is the current query I am using.

SELECT * FROM dbo.tblCustomers
WHERE FirstName LIKE '% %'
       OR
      LastName LIKE '% %'

Update*** Basically I want to return data that does NOT have spaces to the left or right of the name only somewhere in the middle of the name.

Upvotes: 0

Views: 469

Answers (2)

Error_2646
Error_2646

Reputation: 3801

This should work, tag your DBMS though and check the docs for whether it's length function considers trailing spaces. I'm assuming SQL server and using datalength.

SELECT *
FROM dbo.tblCustomers
WHERE (
    FirstName LIKE '% %'
    AND DATALENGTH(TRIM(FirstName)) = DATALENGTH(FirstName)
      )
    OR
    (
    LastName LIKE '% %'
    AND DATALENGTH(TRIM(LastName)) = DATALENGTH(LastName)
      );

Upvotes: 1

Henrik
Henrik

Reputation: 2921

SELECT *
FROM dbo.tblCustomers
WHERE
    TRIM(FirstName) LIKE '% %'
    OR
    TRIM(LastName) LIKE '% %'

Upvotes: 3

Related Questions