Substring in SQL

Question

I have a table

doctors (
id int,
name varchar(255)
)

where name like "Sername Name".

In query i need only Sername of doctor. How can i do it with standard or with solution on most RDBMS?

I know only two solution.

First is SUBSTRING(input SIMILAR pattern ESCAPE escape-char) in postgres is SUBSTRING(input FROM pattern-string).

Second is in postgres like substring(name, 0 , position(' ').

UPD: Is it normal to ask rdbms to split string or better do it manualy in code?

Answer 1

Better yet, make a schema that doesn't have the surenames and first names mixed in the same column:

doctors ( id int, firstname varchar(255), lastname varchar(255) )

Then you don't need those (slow) string operations.

Answer 2

See http://sqlnut.atw.hu/sqlnut2-chp-4-sect-4.html

Search for substring and position.