September 3, 2010 at 11:25 am
I am trying to take info that is in 1 field and break it into 2.The problem is that the info in the field is all different sizes.
The info is an address. for example
change street, 44319
someother street, 44444
so it is a street name and a zip separated by a comma.
is there sql code that can grab this field, and use the comma as a delimiter and put the values into 2 different fields?
Not sure if I explained this well, Thanks in advance for any help.
September 3, 2010 at 12:15 pm
Hey Jatrix,
Wayne will swing by I hope with his link snippet to one of the cleanest functions I've seen regarding separating delimited lists, but in your case, I think the fastest way is:
LEFT( @field, charindex(',' , @field)-1) -- -1 to remove the comma
RIGHT( @field, LEN( @field) - CHARINDEX( ',' , @field) +1) -- +1 to remove the comma here.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 3, 2010 at 12:28 pm
As requested: Delimited Split Function
Usage:
DECLARE @test-2 TABLE (Col1 varchar(100));
INSERT INTO @test-2
SELECT 'change street, 44319' UNION ALL
SELECT 'someother street, 44444';
WITH CTE AS
(
SELECT *
FROM @test-2
CROSS APPLY dbo.DelimitedSplit8K(Col1, ',')
)
SELECT Col1,
Field1 = max(CASE WHEN ItemID = 1 THEN Item ELSE NULL END),
Field2 = max(CASE WHEN ItemID = 2 THEN ltrim(Item) ELSE NULL END)
FROM CTE
GROUP BY Col1;
Though I do think that Craig's solution may end up better a better performing solution over a large record set.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 3, 2010 at 12:54 pm
will this update the fields in the table or just fix results? I need the table updated.
September 3, 2010 at 1:00 pm
Well, since we only have psuedocode from you, you'll have to include our results and translate it into whatever you've got.
Basically, for the above calculations:
UPDATE
Field1 = LEFT(...),
Field2 = RIGHT(...)
FROM
WHERE
clause
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 4, 2010 at 3:28 am
Another approach is to create a function in situ (here called Z) to split the column in to its component parts and then CROSS APPLY against this function which is in a simple 1:1 relationship.
--SELECT Version
WITH cte (col) AS
(
SELECT 'change street, 44319' UNION ALL
SELECT 'someother street, 44444'
)
SELECT col, Field1, LTRIM(Field2) AS Field2 FROM cte
CROSS APPLY
(
VALUES (LEFT(col, CHARINDEX(',', col) - 1),
RIGHT(col, CHARINDEX(',', REVERSE(col)) - 1))
) AS Z (Field1, Field2);
--UPDATE Version
DECLARE @t TABLE (col varchar(100), Field1 varchar(50), Field2 varchar(50))
INSERT @t (col)
VALUES ('change street, 44319'), ('someother street, 44444');
SELECT col, Field1, Field2 FROM @t;
UPDATE @t
SET Field1 = Z.Field1, Field2 = LTRIM(Z.Field2)
FROM @t
CROSS APPLY
(
VALUES (LEFT(col, CHARINDEX(',', col) - 1),
RIGHT(col, CHARINDEX(',', REVERSE(col)) - 1))
) AS Z (Field1, Field2);
SELECT col, Field1, Field2 FROM @t;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply