August 2, 2011 at 10:59 am
Hello Everyone
I am still working on this antiquated piece of junk ETL process.
There is some data that is the Full Name of a person, separated by the caret. I need to separate each name and insert that into each separate column in a table. I have a table with FullName, FirstName, MiddleName, LastName.
The FullName contains COYOTE^WILE^E in the format of LastName^FirstName^MiddleName
I am trying to separate that out into each single variable. I can easily perform the variable assignment, what I am having issue with is getting the entire string separated.
Any and all suggestions or help would be greatly appreciate. I am more of a DBA, and not a SQL Developer.
Thanks in advance
Andrew SQLDBA
August 2, 2011 at 11:01 am
You can probably do it with a combination of LEFT, RIGHT, and CHARINDEX
August 2, 2011 at 11:23 am
I wouldn't even try to roll your own. Take a look at Jeff Moden's string parser[/url]. What you have is a delimited string using ^.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 2, 2011 at 11:23 am
I have tried CHARINDEX, even after replacing the caret with a numeric value, and CHARINDEX is still returning a 0
I replaced the caret with the number 8, which will never be found in a human name, and CHARINDEX cannot find the starting position of the 8. I thought it may be having issues with multiple 8's in the string, so I created a string with only one, still not telling me the starting point.
This is my code and result:
DECLARE @FullName varchar(25)
SET @FullName = 'COYOTE^WILE^E'
SET @FullName = REPLACE(@FullName,'^','8')
SELECT @FullName -- = COYOTE8WILE8E
SELECT CHARINDEX(@FullName,'8',1) -- = 0
What can I be doing incorrectly?
Thanks
Andrew SQLDBA
August 2, 2011 at 11:25 am
You got your parameters mixed up in CHARINDEX. 😉
DECLARE @FullName varchar(25)
SET @FullName = 'COYOTE^WILE^E'
--SET @FullName = REPLACE(@FullName,'^','8')
SELECT @FullName -- = COYOTE8WILE8E
SELECT CHARINDEX('^', @FullName,1) -- = 0
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 2, 2011 at 11:57 am
Oh dang, I cannot believe that I cannot read the SQL BOL.
I think that it may be time for lunch.
I am still missing something. I can get the firstname fine, but not sure how to go on past the first caret to the next one.
I will keep working on it
Thanks
Andrew SQLDBA
August 2, 2011 at 11:58 am
August 2, 2011 at 12:08 pm
Are you quoting me to say that my suggestion is also yours or did you forget to include your comments?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 2, 2011 at 12:11 pm
Sean Lange (8/2/2011)
Are you quoting me to say that my suggestion is also yours or did you forget to include your comments?
repointing to the correct answer.
Might also point out the pivot :
SELECT MAX(CASE WHEN Rowid = 1 THEN Value END) AS FName.... FROM dbo.Split(Col, '^')
August 2, 2011 at 12:46 pm
How about something like this?
Create a function to handle the parsing you need, then just call it when doing the INSERTS
Create scalar function
USE [F1Settings]
GO
/****** Object: UserDefinedFunction [dbo].[fx_FormatArrayText] Script Date: 08/02/2011 13:41:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* ############################################################################################################### */
--FORMAT DELIMITED STRING
/* ############################################################################################################### */
ALTER FUNCTION [dbo].[fx_FormatArrayText] (
@String varchar(1500),
@Delimiter char(1),
@NumberQuotes int = 1)
RETURNS varchar(1500) AS
/*
----------------------------------------------------------------------------------------------------------------
Purpose:Convert delimited text within a string into parenthesized values (quotes optional)
----------------------------------------------------------------------------------------------------------------
NOTES:@Delimiter - Tells function the delimiter to parse the text with
@NumberQuotes - How many quotes you wisdh to have in the OUTPUT string
----------------------------------------------------------------------------------------------------------------
Created On:10/20/2005
Create By:MyDoggieJessie
----------------------------------------------------------------------------------------------------------------
SELECT dbo.fx_FormatArrayText('COYOTE^WILE^E','^', 1)
*/
BEGIN
DECLARE @Quote varchar(10)
SET @Quote = ''
/* ######################################### START MAIN FUNCTION HERE ########################################## */
IF @NumberQuotes >= 1
BEGIN
SET @Quote = SPACE(@NumberQuotes)
SET @Quote = REPLACE(@Quote, ' ', '''')
END
IF @Delimiter = ' '
BEGIN
/* Eliminate double spaces in text string */
WHILE CHARINDEX(' ', RTRIM(@String)) <> 0
BEGIN
SET @String = REPLACE(@String, ' ', ' ')
END
END
ELSE
BEGIN
/* Eliminate all spaces in text string */
WHILE CHARINDEX(' ', RTRIM(@String)) <> 0
BEGIN
SET @String = REPLACE(@String, ' ', '')
END
END
/* Convert supplied delimiter with open quotes, comma, and close quotes */
SET @String = REPLACE(@String, @Delimiter, @Quote + ',' + @Quote)
/* Add opening and closing quotes and parentheses */
SET @String = @Quote + @String + @Quote
/* ########################################## END MAIN END HERE ########################################### */
RETURN @String
END
/*
SELECT dbo.fx_FormatArrayText('IT|HR|ACCOUNTING|SALES','|', 1)
*/
Put the function to use
DECLARE @SQL varchar(200), @String varchar(50)
SET @String = dbo.fx_FormatArrayText('COYOTE^WILE^E','^', 1)
CREATE TABLE #MyTable (FirstName varchar(15), LastName varchar(15), Initial varchar(1))
SET @SQL = 'INSERT INTO #MyTable SELECT ' + RTRIM(@String)
EXEC (@SQL)
SELECT * FROM #MyTable
DROP TABLE #MyTable
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 2, 2011 at 12:50 pm
August 2, 2011 at 1:05 pm
Ninja's_RGR'us (8/2/2011)
Sean Lange (8/2/2011)
I wouldn't even try to roll your own. Take a look at Jeff Moden's string parser[/url]. What you have is a delimited string using ^.For the 3rd time:exclamation:.
Fast, tested, works, use it all the time here.
In case you missed Rimi's point or mine...it is FAST FAST FAST!!!!!!
WAY FASTER than ANY kind of looping scalar function. Period. No argument, no discussion. It has been tested and put through the ringer by some of the smartest sql minds out there.
@MyDogJessie, please take a look at this. It will blow the doors of your rbar function anyday.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 2, 2011 at 1:07 pm
Thanks, I will look at replacing ours with this nifty code!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply