'LIKE' Statement on two separate words

[gbhs]

Hi Guys
I have a "StudentNames" column in an sql table.
Each student in the table has atleast two names all in one column.
Assume that the following as studentNames.

StudentNames
-------------
John Smith
Joseph Smitter
Johan Smittin

How can I use the LIKE statement to select

students with same first 3 letters of first name and
same first 3 letters of second name and possibly third name.

I mean a query in the lines of:

SELECT StudentNames 
FROM MyTable 
WHERE StudentNames LIKE 'joh%' and (substring(....'smi%')

The expected result should be

StudentNames
-------------
John Smith
Johan Smitten

Your Help is Very Much Appreciated
sql Newbie

[Reverend Jim]

Rather than trying to code up a convoluted query to do that you should instead restructure your table to have separate fields for given name(s) and surname. You run into problems when storing the names as "John Smith". For one thing, if you want to sort by last name you have to parse. Another thing is difficulty in parsing last names that consist of more than one word. For example, how do you handle "Jon van der Hoerst"? Structure the fields as

LastName
FirstName

and you can easily combine them in a query or create a view that combines the names.

[gbhs]

The reason is that I have well over 3000 names to import.
Its a hell of time separating them into FirstName LastName.
In order to avoid duplicates in names when inserting new names.
I want to check if such similar name exist before inserting.
We dont have names like Jon van der Hoerst in my area.Checking
Jon van der is still fine for me.
Any Help?
SQL Newbie

[rch1231]

Hello,

You have to treat them as two separate conditions and you need to code for capitalization like this:

WHERE StudentNames LIKE 'joh%' and (StudentNames LIKE '%smi%' or StudentNames LIKE '%Smi%')

[pritaeas]

In order to avoid duplicates in names when inserting new names.

Why not use a unique index on that column?

[gbhs]

With Indexes, if John Smith is in the table and a user erroneously adds the name John Smit we may end up with 2 records for 1 person(which is what I want to avoid). Can Indexes check this?

SQL Newbie

[pritaeas]

Can Indexes check this?

Yes. A unique index will prevent insertion of a duplicate.

[gbhs]

This may be foolish of me but how can I set a unique index on StudentNames.
I may have been doing this in other table ID columns (in SSMS) without knowing.
SQLNewbie

[Reverend Jim]

If you want to do this using a query you can execute

CREATE UNIQUE NONCLUSTERED INDEX IX_StudentNames 
    ON MyTable (StudentNames ASC)
  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
        SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
        DROP_EXISTING = OFF, ONLINE = OFF, 
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
    ON [PRIMARY]

[gbhs]

Thanks to you guys.
The query worked.
I realised I could set unique index from SSMS.
SQLNewbie