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?

Is it normal to ask rdbms to split string or better do it manualy in code? — den bardadym
– den bardadym, Commented Jun 26, 2010 at 17:28
As I've written in my answer, better do it in the schema (i.e. before inserting the data). — Janick Bernet
– Janick Bernet, Commented Jun 26, 2010 at 17:33
@inflagranti. I know that this table has bad design, but i have such table. My task is parse string. Question is how to parse string in sql or if it is slow solution make it manually. — den bardadym
– den bardadym, Commented Jun 26, 2010 at 17:36
@den bardadym. It is not slower in SQL than in your client code (it even has the advantage that only the relevant portion has to be transfered). It's just much slower and - as, pointed out by mjv, more error prone - than a decent schema. — Janick Bernet
– Janick Bernet, Commented Jun 26, 2010 at 17:56

František Žiačik · Accepted Answer · 2010-06-26 17:24:18Z

1

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

Search for substring and position.

answered Jun 26, 2010 at 17:24

František Žiačik

7,6041 gold badge38 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mjv Over a year ago

Furthermore, and not withstanding the possible irregularities introduced in the building of this particular database instance, peoples' names can be irregular enough to make parsing the surname a non trivial task (ex: mulitiple tokens with "Di Angelo", very short surnames "Le", "Zef" etc.)

den bardadym Over a year ago

Thanks for link. It is interesting.

den bardadym Over a year ago

I accept your answer. Because other answer not for my question. Thanks.

Janick Bernet · Accepted Answer · 2010-06-26 17:30:04Z

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.

answered Jun 26, 2010 at 17:30

Janick Bernet

21.2k2 gold badges33 silver badges56 bronze badges

Collectives™ on Stack Overflow

Substring in SQL

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related