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 |
|
rubs_65
Posting Yak Master
144 Posts |
 Posted - 2004-09-30 : 20:37:20
|
| Hi, I have a table in which name is stored as as single column fullname and now we need to extract the first,middle and last name from this column and populate into other table with having 3 columns. middle name is not required..the name have only 2 or 3 words seperated by space and we need to extract and populate the other table....logic also will make sure if there are only 2 words then it should skip middle name.....how can we write the query to do this? names look like: A A Shandrowsky A Angelo A B Wallace A Bergman Thanks --Harvinder |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-01 : 02:43:12
|
| search for fnSplitText function in this forum, it's pretty neat. If you pass 'A A Shandrowsky' , it will return a table with 3 rows or you can edit the function to return 3 values @fname,@mname,@lname.--edit, here's an edited version of that functionCREATE FUNCTION dbo.fnSplitName( @RowData nvarchar(2000), @SplitOn nvarchar(5)) RETURNS @rtnTable table (fname nvarchar(20),mname nvarchar(20),lname nvarchar(20)) AS BEGIN Declare @Cnt int,@fname nvarchar(20),@mname nvarchar(20),@lname nvarchar(20) Set @Cnt = 1 While (Charindex(@SplitOn,@RowData)>0) Begin if @fname is null set @fname=ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1))) else if @mname is null set @mname=ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1))) Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData)) Set @Cnt = @Cnt + 1 End if @lname is null set @lname=@rowdata insert into @rtnTable select @fname,@mname,@lname returnENDexample:insert into tablenameselect fname,mname,lname from fnsplitname('j a doe',' ') |
 |
|
|
|
|
|
|
|
query problem