CODEWITHSUNDEEP
sqlsql-servert-sql
9684921
9684921

Reputation: 31

How can I find a string separated by a space in SQL

I am trying to find a way where I can skim out records of customers where 'First Name' and 'Middle Name' has been entered in the first_name column in the customer detail table. For examples

first_name, Last_name, mobile_no
Mary Jane     Smith     0400000000
Shane Angus   John      0400000000
Rudy          Gill      0401111111
Rachel        Rose

from the above examples I only want to find records

Mary Jane     Smith     0400000000
Shane Angus   John      0400000000

Upvotes: 1

Views: 1475

Answers (2)

Doua
Doua

Reputation: 183

here's another solution for you if you want to get more complex and account for the middle name.

create table test (first_name nvarchar(50), middle_name nvarchar(50), last_name nvarchar(75), mobile_no nvarchar(10))

insert test
select 'mary jane', null, 'smith', '0400000000'
union all
select 'shane angus', null, 'john', '0400000000'
union all
select 'rudy', 'jacob', 'gill', '0401111111'
union all
select 'rachel', 'liza', 'rose', '0400000000'

select *
from test
where middle_name is null and charindex(char(32),first_name) > 0

this won't help you though if the first name is double-worded like Bobby Jo hence the middle name check.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269583

You can use like:

select t.*
from t
where first_name like '% %';

Note: This just checks for a space. It does not guarantee that one of the names is a middle name.

Upvotes: 3

Related Questions