Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cipriani1984
Constraint Violating Yak Guru
304 Posts |
 Posted - 2010-09-29 : 09:34:45
|
| Hi,I have the current name field which is abit annoying:field1 = 'surname/fname title'How would I return everything in the field before the white space?So im after 'surname/fname' not including the 'title'.Thanks in advance. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-09-29 : 09:40:50
|
| select left(field1, charindex (' ' , field1) - 1) |
 |
|
|
cipriani1984
Constraint Violating Yak Guru
304 Posts |
Posted - 2010-09-29 : 09:52:23
|
I get the following error?'Invalid length parameter passed to the SUBSTRING function'quote:
Originally posted by russell
select left(field1, charindex (' ' , field1) - 1)
|
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-09-29 : 09:59:25
|
| then you have records without a space in the table. |
|
|
|
cipriani1984
Constraint Violating Yak Guru
304 Posts |
Posted - 2010-09-29 : 10:16:36
|
Ok ive managed to fix that no problem, just have another question.What about if the field was like this'Sname / Fname Title'Is there anyway to get the names again without title or is that even harder??quote:
Originally posted by russell
then you have records without a space in the table.
|
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-09-29 : 11:35:15
|
| if the space is guaranteed to be the last space in the string, you can find it using the REVERSE() funtion and then CHARINDEX()Have a look at this: http://msdn.microsoft.com/en-us/library/ms181984(v=sql.90).aspx |
|
|
|
|
|
|
select name depending