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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy