SQL - returns a part of a field

Question

Possible Duplicate:
Split string in SQL

I have a column called LastName in the data base that has the following value:

Johnson  (Ext. 12345)

I only want to return the Last name without the extension.

Any idea what command to use in T-SQL? (for SSRS 2008 version)

thanks

Does the lastname always have a space before an open bracket? — Dibstar, Jan 11 '13 at 14:58

score 2 · Answer 1 · answered Jan 11 '13 at 13:44

2

give this a try,

SELECT RTRIM(SUBSTRING(LastName, 1, CHARINDEX('(', LAstName) - 1)) LastName
FROM table1

SQLFiddle Demo

answered Jan 11 '13 at 13:44

John Woo

258,903
69
498
492

score 1 · Answer 2 · answered Jan 11 '13 at 13:45

1

You can;

left(fld, charindex(' ', fld + ' ', 1) - 1)

answered Jan 11 '13 at 13:45

Alex K.

171,639
30
264
288

score 0 · Answer 3 · edited May 23 '17 at 12:27

0

First allow me to say that such composite values in a single field are against normalization, present various problems regarding the integrity, duplication etc of data and also present problems on simple queries as the one you are trying to perform.

If you can you should split Last name and Ext in two different columns.

For answers to you question check this

edited May 23 '17 at 12:27

Community

1
1

answered Jan 11 '13 at 13:47

B11

223
3
12

Yes but naming conventions vary across cultures. Some people have four, five six or more names: how many columns is enough? – APC Jan 13 '13 at 05:12
well in the case that you do not know the number of columns to associate, you can take a different approach, you can keep such values as rows of a different table, and relate to them via a foreign key, where one row on the main table can be associated to many rows on the secondary table. But this is just one approach, depending on the domain there can be various solutions. – B11 Jan 13 '13 at 20:04

SQL - returns a part of a field

3 Answers3