Extract Data From A Column

Hi ,
I have to extract City,State and Zip Code from the below
column and insert it separately in 3 columns. how can i
write my select statements so i can get
City=North Bergen
State=NJ
Zip =07057
Site
--
NORTH BERGEN, NJ, 07057
Springfield, IL, 62704
MANASQUAN, NJ, 08736
BLOOMINGTON, MN, 55425As long as you have a uniform delimiter, you can use the following proc to
parse the tokens and load them appopriately.
You tweak the stored proc to handle the tokens as you wnat.
--
HTH
Satish Balusa
Corillian Corp.
Create Procedure sp_ParseArrayAndLoadTable ( @.Array varchar(1000),
@.Separator char(1) ,
@.LoadTableName sysname OUT
)
AS
BEGIN
SET NOCOUNT ON
-- @.Array is the array we wish to parse
-- @.Separator is the separator charactor such as a comma
DECLARE @.separator_position int -- This is used to locate each separator
character
DECLARE @.array_value varchar(1000) -- this holds each array value as it is
returned
-- For my loop to work I need an extra separator at the end. I always look
to the
-- left of the separator character for each array value
SET @.array = @.array + @.separator
-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @.separator + '%' , @.array) <> 0
BEGIN
-- patindex matches the a pattern against a string
SELECT @.separator_position = PATINDEX('%' + @.separator + '%' , @.array)
SELECT @.array_value = LEFT(@.array, @.separator_position - 1)
-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @.array_value holds the value of this element of the array
-- Do the job whatever you wanted to do
SELECT Array_Value = @.array_value
-- This replaces what we just processed with and empty string
SELECT @.array = STUFF(@.array, 1, @.separator_position, '')
END
SET NOCOUNT OFF
END
GO
"Mohamadi.Slatewala@.wellsfargo.com" <anonymous@.discussions.microsoft.com>
wrote in message news:22f301c3e12f$9d1dbc30$a301280a@.phx.gbl...
> Hi ,
> I have to extract City,State and Zip Code from the below
> column and insert it separately in 3 columns. how can i
> write my select statements so i can get
> City=North Bergen
> State=NJ
> Zip =07057
> Site
> --
> NORTH BERGEN, NJ, 07057
> Springfield, IL, 62704
> MANASQUAN, NJ, 08736
> BLOOMINGTON, MN, 55425
>

Extract a complete XML section and sub sections

Ive been using select statments to get information from each 'section'.
example below shows only for the Header section.
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
Select * from OpenXML(@.hDoc, '//Header') with
(reportType varchar(10), reportNumber VarChar(6), batchNumber varchar(6),
reportSequenceNumber varchar(6), userNumber varchar(6) )
EXEC sp_xml_removedocument @.hDoc
the following section of the file has sub sections contained within the
Header section, Is there a simple way to return all the data values in one
SQL ?
- <Header reportType="REFT2013" reportNumber="14685" batchNumber="023"
reportSequenceNumber="000760" userNumber="948053">
<ProducedOn time="17:31:38" date="2004-09-27" />
<ProcessingDate date="2004-09-28" />
</Header>
You can specify relative XPaths for the columns in the subelements, as shown
below. Is that what you mean?
DECLARE @.TESTXML nvarchar(2000)
DECLARE @.hDoc integer
SET @.TESTXML =
'<Header reportType="REFT2013" reportNumber="14685" batchNumber="023"
reportSequenceNumber="000760" userNumber="948053">
<ProducedOn time="17:31:38" date="2004-09-27" />
<ProcessingDate date="2004-09-28" />
</Header>'
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
Select * from OpenXML(@.hDoc, '//Header', 1)
with
(reportType varchar(10),
reportNumber VarChar(6),
batchNumber varchar(6),
reportSequenceNumber varchar(6),
userNumber varchar(6),
ProducedOnTime nvarchar(10) 'ProducedOn/@.time',
ProducedOnDate nvarchar(20) 'ProducedOn/@.date',
ProcessingDate nvarchar(20) 'ProcessingDate/@.date' )
EXEC sp_xml_removedocument @.hDoc
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:4CD137BC-09E8-42A8-BC86-6C6991EA979C@.microsoft.com...
Ive been using select statments to get information from each 'section'.
example below shows only for the Header section.
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
Select * from OpenXML(@.hDoc, '//Header') with
(reportType varchar(10), reportNumber VarChar(6), batchNumber
varchar(6),
reportSequenceNumber varchar(6), userNumber varchar(6) )
EXEC sp_xml_removedocument @.hDoc
the following section of the file has sub sections contained within the
Header section, Is there a simple way to return all the data values in one
SQL ?
- <Header reportType="REFT2013" reportNumber="14685" batchNumber="023"
reportSequenceNumber="000760" userNumber="948053">
<ProducedOn time="17:31:38" date="2004-09-27" />
<ProcessingDate date="2004-09-28" />
</Header>
sql

Extended Stored Procedure

When I run the below (sp_OACreate) in SQL Query Analyzer it runs fine. When
I add the same to a stored proc my app hangs and I get a timeout.
Any help is greatly appreciated. I really don't want to create a job that
runs this.
EXEC @.hr = sp_OACreate 'MyLocalEXE.Host', @.object OUT, 4
IF @.hr <> 0 BEGIN EXEC sp_raiseoaerror @.object, @.hr RETURN (-1) ENDHi
At a guess as the application hangs it probably means it can't find the
object, which implies it is not being run in the same context and/or possibl
y
a lack of permissions. You also need to be symin to run sp_OACreate.
John
"Mark - HYPERe" wrote:

> When I run the below (sp_OACreate) in SQL Query Analyzer it runs fine. Wh
en
> I add the same to a stored proc my app hangs and I get a timeout.
> Any help is greatly appreciated. I really don't want to create a job that
> runs this.
> EXEC @.hr = sp_OACreate 'MyLocalEXE.Host', @.object OUT, 4
> IF @.hr <> 0 BEGIN EXEC sp_raiseoaerror @.object, @.hr RETURN (-1) END