Split name field into 3 fields
The name column (col002) is in this format
‘title fName sName’
I would like to update the table so that I have 3 fields for the name, title, fName and sName
I have had a look at the data and there are some fields that do not conform to the ‘title fName sName’ layout.
Can I use some kind of IF to do this, ignoring any that don’t conform?
TIA
As
Declare @TEMP_COL002 AS VARCHAR(100)
DECLARE @TEMP_COL1 AS VARCHAR(30)
DECLARE @TEMP_COL2 AS VARCHAR(30)
DECLARE @TEMP_COL3 AS VARCHAR(30)
DELCARE @TEMP_VAL AS VARCHAR(100)
Create Table #TempTable(Col002 varchar(100), Col1 Varchar(30),Col2 Varchar(30),Col3 Varchar(30))
DECLARE temp_cursor CURSOR
FOR SELECT col002 FROM YourTable
OPEN temp_cursor
FETCH NEXT FROM temp_cursor into @TEMP_COL002
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TEMP_COL1 = ”
SET @TEMP_COL2 = ”
SET @TEMP_COL3 = ”
IF(PATINDEX(‘% %’,@TEMP_COL002) <> 0)
BEGIN
SET @TEMP_COL1 = LEFT(@TEMP_COL002,PATINDEX
SET @TEMP_COL002 = RIGHT(@TEMP_COL002,LEN(@TEMP_COL002) – PATINDEX(‘% %’,@TEMP_COL002) + 1)
IF(PATINDEX(‘% %’,@TEMP_COL002) <> 0)
BEGIN
SET @TEMP_COL2 = LEFT(@TEMP_COL002,PATINDEX(‘% %’,@TEMP_COL002) – 1)
SET @TEMP_COL3 = RIGHT(@TEMP_COL002,LEN(@TEMP_COL002) – PATINDEX(‘% %’,@TEMP_COL002) + 1)
END
END
IF(@TEMP_COL1 <> ” AND @TEMP_COL2 <> ” AND @TEMP_COL3 <> ”)
INSERT INTO #TEMPTABLE values(@TEMP_COL002,@TEMP_COL1,@TEMP_COL2,@TEMP_COL3)
FETCH NEXT FROM temp_cursor into @TEMP_COL002
END
Select * from #TempTable
END
