CODEWITHSUNDEEP
sqlsql-server
user9793591
user9793591

Reputation:

Getting a substring delimited by multiple characters in sql

First off, I know a variation of this question has been asked many times but I've found no answers anywhere online for the following.

I have a table of forenames. Some of the names are double barreled i.e. Mary-Jane, Barbara Ann. I need to get a substring of the names, extracting just the first part of the name, i.e. Mary, Barbara.

The problem is that in the table, some are split using spaces ( ) and some are split using dashes (-).

This is the query I have currently:

SELECT forename, LEFT(forename, charindex(' ', forename) - 0) changedName
from student

The first argument of charindex can be a space or a dash, both work fine but I need a way to run the query that will remove both dashes and spaces.

All names must be in the one column.

Using MSSQLS 2014.

What is the best way to so this?

Upvotes: 1

Views: 893

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Here is a simple method:

SELECT forename,
       LEFT(forename, patindex('%[ -]%', forename) - 1) as changedName
FROM student;

If some students have single names (neither character), you can do:

SELECT forename,
       LEFT(forename, patindex('%[ -]%', forename + '-') - 1) as changedName
FROM student;

Upvotes: 3

Related Questions