March 23, 2005 at 12:32 pm
Hi - On 1 SQL 2000 table, I have a single column comprised of data representing either:
- CITY, STATE, COUNTRY (eg: 'San Francisco, CA, USA')
or
- CITY, STATE (eg: 'Palos Verdes, CA')
Can someone help me code T-SQL to parse this data into 3 columns?
eg. COL1 contains City, COL2 contains State, COL3 contains Country
I saw this solution but it parses FIRST & LAST Name delimited by a space:
CREATE TABLE
A_Table (FullName varchar(50),
FirstName
varchar(25),
LastName
varchar(25))
INSERT INTO
A_Table (FullName) VALUES ('Homer Simpson')
INSERT INTO
A_Table (FullName) VALUES ('F Flintstone')
UPDATE
A_Table
SET FirstName = LEFT(FullName,CHARINDEX(' ',FullName) - 1),
LastName =
RIGHT(FullName,LEN(FullName) - CHARINDEX(' ',FullName))
SELECT
* FROM A_Table
DROP TABLE
A_Table
--thx in advance
March 23, 2005 at 1:38 pm
How bout
DECLARE @Text VARCHAR(255)
DECLARE @VAR1 VARCHAR(50)
DECLARE @VAR2 VARCHAR(50)
DECLARE @VAR3 VARCHAR(50)
DECLARE @Dlm1 INTEGER
DECLARE @Dlm2 INTEGER
DECLARE @Diff INTEGER
DECLARE @3Col BIT
SET @Text = 'San Francisco, CA'
SET @Dlm1 = (SELECT CHARINDEX(',', @Text))
SET @Dlm2 = (SELECT CHARINDEX(',', @Text, (@Dlm1 + 1)))
SET @3Col = 1
IF @Dlm2 = 0 SET @Dlm2 = @Dlm1
SET @Diff = (@Dlm2 - (@Dlm1 + 1))
IF @Diff < 0
BEGIN
SET @Diff = LEN(@Text) - @Dlm1
SET @3Col = 0
END
SET @VAR1 = LEFT(@Text, (@Dlm1 - 1))
SET @VAR2 = SUBSTRING(@Text, (@Dlm1 + 1), @Diff)
IF @3Col = 1 SET @VAR3 = RIGHT(@Text, @Diff)
SELECT @VAR1, @VAR2, @VAR3
Good Hunting!
AJ Ahrens
webmaster@kritter.net
March 24, 2005 at 1:25 am
Not sure, how or why you want to parse string like 'San Francisco, CA, USA' into 3 columns as this would still leave non-atomic data in one column, thus violating 1NF, but what about:
declare @string varchar(100)
set @string = 'San Francisco, CA, USA'
select
parsename(replace(replace(@string,',',''),' ','.'),4)
, parsename(replace(replace(@string,',',''),' ','.'),3)
, parsename(replace(replace(@string,',',''),' ','.'),2)
, parsename(replace(replace(@string,',',''),' ','.'),1)
set @string = 'Palos Verdes, CA'
select
parsename(replace(replace(@string,',',''),' ','.'),3)
, parsename(replace(replace(@string,',',''),' ','.'),2)
, parsename(replace(replace(@string,',',''),' ','.'),1)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
March 24, 2005 at 9:13 am
Another way is to create a UDF, which you can then use for future tasks as well. I use the following UDF, fGetToken(), to parse delimited data sets, but you can use it here if you LTRIM() the return value. Alternatively, you could LTRIM() the value in the UDF.
Anyway, here's the code:
CREATE FUNCTION dbo.fGetToken
(
@parm varchar(8000) -- the source data
, @delim varchar(100) -- the delimiter
, @whichOccur smallint -- position of desired value, from the left
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @occur int, @spos int
DECLARE @token varchar(8000)
SET @occur = 0
WHILE @occur < @whichOccur AND @parm <> '' AND @parm IS NOT NULL
BEGIN
SET @spos = CHARINDEX( @delim , @parm, 1 )
IF @spos = 0
BEGIN
SET @token = @parm
SET @parm = ''
END
ELSE
BEGIN
SET @token = SubString( @parm, 1, @spos - 1)
SET @parm = Substring( @parm, @spos + Len(@delim), Len(@parm) - @spos )
END
SET @occur = @occur + 1
END
IF @occur <> @whichOccur
SET @token = '' -- or NULL, if desired
-- RETURN LTrim(@token)
RETURN @token
END
GO
---------------------------------------------------------
-- EXAMPLE
---------------------------------------------------------
CREATE TABLE #places
(
id int PRIMARY KEY IDENTITY(1,1)
, place varchar(40)
)
SET NOCOUNT ON
INSERT #places (place) VALUES ('San Francisco, CA, USA')
INSERT #places (place) VALUES ('Palos Verdes, CA')
INSERT #places (place) VALUES ('Atlanta, GA, USA')
INSERT #places (place) VALUES ('Seattle, WA')
SET NOCOUNT OFF
-- Return values are varchar(8000)
SELECT place
, dbo.fGetToken(place, ', ', 1) AS city
, dbo.fGetToken(place, ', ', 2) AS state
, dbo.fGetToken(place, ', ', 3) AS country
FROM #places
-- Return values are prettied up a bit
SELECT place
, CONVERT(varchar(30), LTrim(dbo.fGetToken(place, ',', 1))) AS city
, CONVERT(varchar(2), LTrim(dbo.fGetToken(place, ',', 2))) AS state
, CONVERT(varchar(5), LTrim(dbo.fGetToken(place,',', 3))) AS country
FROM #places
DROP TABLE #places
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply