August 6, 2004 at 10:17 am
I am migrating data from one table into another; in the old table, there is a single name column (first last), but in the new table, I want them seperated into 2 columns (firstname, lastname)...Basically, I need a function that I identifies the place of the space between the first & last names (like the InStr function in Access), but I don't see any SQL server string functions that can do this....any suggestions?
August 6, 2004 at 10:57 am
CHARINDEX or PATINDEX (reference in BOL) will perform what you are looking for 🙂
Good Hunting!
AJ Ahrens
webmaster@kritter.net
August 9, 2004 at 11:18 am
You could try something like:
select top 5 left(Full_Name_Field, CHARINDEX (' ',Full_Name_Field, 1)) as First_Name,
right(rtrim(Full_Name_Field), len(Full_Name_Field) - CHARINDEX (' ',Full_Name_Field, 1)) as Last_Name,
from customer_Table
where Full_Name_Field like '% [a-z]%'
update customer_Table
set
First_Name = left(Full_Name_Field, 0, CHARINDEX (' ',Full_Name_Field, 1))),
Last_Name = right(Full_Name_Field, len(Full_Name_Field) - CHARINDEX (' ',Full_Name_Field, 2)),
updated = 1 -- This would be a bit flag that would be updated to let you know that this record does not have to be checked again.
from customer_Table
where Full_Name_Field like '% %'
and updated <> 1
August 9, 2004 at 2:41 pm
Here is a proc that demonstrates parsing a string out by a delimeter into multiple words. It should work to suit your purpose directly, or as an example of how to use the charindex functions to accomplish what you need to do. I have a version written as a function as well, but this should be more specific to your needs. It will work on sql 7.0 and 2000.
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
create procedure sp__ParseWordsToTable
@string varchar(255)
,@delimeter varchar(1) = ' ' --default to space delimeted.
,@word1 varchar(30) = null OUTPUT
,@word2 varchar(30) = null OUTPUT
,@word3 varchar(30) = null OUTPUT
,@word4 varchar(30) = null OUTPUT
,@word5 varchar(30) = null OUTPUT
,@word6 varchar(30) = null OUTPUT
,@word7 varchar(30) = null OUTPUT
,@word8 varchar(30) = null OUTPUT
,@word9 varchar(30) = null OUTPUT
,@word10 varchar(30) = null OUTPUT
,@word11 varchar(30) = null OUTPUT
,@word12 varchar(30) = null OUTPUT
,@word13 varchar(30) = null OUTPUT
,@word14 varchar(30) = null OUTPUT
,@word15 varchar(30) = null OUTPUT
,@word16 varchar(30) = null OUTPUT
,@word_count tinyint = 0 OUTPUT
,@resultset_yn tinyint = 0
AS
/*
********************************************************************************
Purpose: Parse words from values from a delimited string
& insert them into a temp table or return them as output parameters
@id_txt - an array of combined coverage ids
Copyright 1996, 1997, 2000 Clayton Groom (clayton_groom@hotmail.com)
Note: only allows for delimiters of 1 character in length
********************************************************************************
*/
DECLARE @continue int
,@start_pos int
,@length int
,@delimeter_pos int
,@word varchar(30)
CREATE TABLE #tmp_word (tmp_id int NOT NULL identity(1,1)
,word varchar(30) null)
-- parse the original @string array into a temp table
SELECT @continue = 1
,@start_pos = 1
,@string = RTRIM( LTRIM( @string ))
,@length = DATALENGTH( RTRIM( LTRIM( @string )))
WHILE @continue = 1
BEGIN
SELECT @delimeter_pos = CHARINDEX( @delimeter
, (SUBSTRING( @string, @start_pos
, ((@length - @start_pos) + 1))))
IF @delimeter_pos > 0 -- delimeter(s) found, get the value
BEGIN
SELECT @word = SUBSTRING( @string , @start_pos,
(@delimeter_pos - 1))
SELECT @start_pos = @delimeter_pos + @start_pos
END
ELSE -- No more delimeters, get last value
BEGIN
SELECT @word = SUBSTRING(@string, @start_pos,
((@length - @start_pos) + 1))
SELECT @continue = 0
END
INSERT INTO #tmp_word (word)VALUES( @word )
END
select @word1 = word from #tmp_word where tmp_id = 1
select @word2 = word from #tmp_word where tmp_id = 2
select @word3 = word from #tmp_word where tmp_id = 3
select @word4 = word from #tmp_word where tmp_id = 4
select @word5 = word from #tmp_word where tmp_id = 5
select @word6 = word from #tmp_word where tmp_id = 6
select @word7 = word from #tmp_word where tmp_id = 7
select @word8 = word from #tmp_word where tmp_id = 8
select @word9 = word from #tmp_word where tmp_id = 9
select @word10 = word from #tmp_word where tmp_id = 10
select @word11 = word from #tmp_word where tmp_id = 11
select @word12 = word from #tmp_word where tmp_id = 12
select @word13 = word from #tmp_word where tmp_id = 13
select @word14 = word from #tmp_word where tmp_id = 14
select @word15 = word from #tmp_word where tmp_id = 15
select @word16 = word from #tmp_word where tmp_id = 16
select @word_count = count(*) from #tmp_word
if @resultset_yn = 1
begin
select word from #tmp_word order by tmp_id
end
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
Kindest Regards,
Clayton
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply