CODEWITHSUNDEEP
sqlsql-server-2005
Conger88
Conger88

Reputation: 55

SQL Returning names with multiple spaces

I am looking to return all names with more than one space in a single field. For example 'John Paul Smith'. Using SQL server management studio 2005

Example I have a patients table with forename and surname

I want to return all forenames that have example 'John Paul Smith' in one field.

The query given seems to work on the surname field but not the forename. I knot for certain that the forename columns has these types of data but it is returning no results. Con

Upvotes: 0

Views: 108

Answers (2)

fancyPants
fancyPants

Reputation: 51868

Here are two solutions that in my opinion are easier to read/understand than JohnHC's.

  1. It can't get any simpler. Use wildcards to search for (at least) two spaces.

    SELECT * FROM your_table WHERE your_column LIKE '% % %';

  2. Check the length after replacing the spaces

    SELECT * FROM your_table WHERE LEN(your_column) - LEN(REPLACE(your_column, ' ', '')) >= 2;

Upvotes: 2

JohnHC
JohnHC

Reputation: 11195

Oracle:

SELECT MyField
from MyTable
where REGEXP_INSTR (MyField, ' ', 1, 2, 0, 'i') > 0

SQL server:

SELECT MyField
from MyTable
where CHARINDEX(' ', MyField, charindex(' ',MyField)+1) > 0

MySQL

select MyField
from MyTable
where length(SUBSTRING_INDEX(MyField, ' ', 2)) < length(MyField)

Upvotes: 4

Related Questions