spliting a field - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

spliting a field

Author

Topic

asarak
Starting Member

36 Posts

Posted - 2005-08-23 : 00:17:20

Hi guys, i have a problem with SQL.
I have two fields in a table
Surname char(50) and Name char(30).
In Surname i have some rows with both surnames and names seperated by a space like

Surname | Name
TAYLOR JOHN |
KAGE |NICHOLAS
BEST JOHN |

I want to split the surname data into both fields each for surname and name. And have the following :

Surname | Name
TAYLOR |JOHN
KAGE |NICHOLAS
BEST |JOHN

Any suggesitions

i try to use datalength with substring but with no effect..

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-08-23 : 00:33:40

Hi there. Look into using SUBSTRING with CHARINDEX instead.

Use the CHARINDEX to find the position of the space (if any, be sure to first remove any leading or trailing spaces with LTRIM, RTRIM). Once you have this position, you can use substring to extract the values before and after the space.

Give it a shot and please dont hesitate to repost here if you run into any trouble.

Nathan Skerl

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-23 : 00:35:04

Declare @t varchar(20)
set @t='BEST JOHN'
select left(@t,charindex(' ',@t)),substring(@t,charindex(' ',@t),len(@t))

Madhivanan

Failing to plan is Planning to fail

asarak
Starting Member

36 Posts

Posted - 2005-08-23 : 01:04:10

I dont believe i was so easy....
Thanks a lot it works....
Thanks to all they respond

quote:
Originally posted by madhivanan

Declare @t varchar(20)
set @t='BEST JOHN'
select left(@t,charindex(' ',@t)),substring(@t,charindex(' ',@t),len(@t))

Madhivanan

Failing to plan is Planning to fail

ismail_issac
Starting Member

22 Posts

Posted - 2005-08-23 : 01:09:00

hi there

is this what you wanted ????

update [Your_TableName] set Surname= (select rtrim(ltrim(left(Surname,charindex(' ',Surname)-1)))),
[Name]=rtrim(ltrim(right(Surname,len(Surname)-charindex(' ',Surname)-1)))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources