String as a parameter - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

String as a parameter

Author

Topic

madhan
Yak Posting Veteran

59 Posts

Posted - 2011-10-02 : 10:50:41

Hi - I have the following string that needs to be passed as a parameter to stored procedure and the string needs to be get saved in table which has a "address" column of type xml.

1050 Buckingham Street Watertown C S06795660 . 1050 Buckingham Street Watertown C S06795660 . 7375Â McKNIGHT RD PITTSBURGH P S15237351 . 7375Â McKNIGHT RD PITTSBURGH P S15237351 . 7375Â McKNIGHT RD PITTSBURGH P S15237351 .

Please give me directions on this. I am using sql server 2008. Is it possible to pass this kind of string to stored procedure and save this in table xml type column. I have no idea how to parse the data at .(dot) place?

Thank you for the direction in advance.

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-10-02 : 11:57:45

CREATE TABLE #tblXML
(
Address XML
)
GO

INSERT INTO #tblXML VALUES ('1050 Buckingham Street Watertown C S06795660 . 1050 Buckingham Street Watertown C S06795660 . 7375Â McKNIGHT RD PITTSBURGH P S15237351 . 7375Â McKNIGHT RD PITTSBURGH P S15237351 . 7375Â McKNIGHT RD PITTSBURGH P S15237351 .')
Go

SELECT Address FROM #tblXML
GO

Best Regards
Vadivel

http://vadivel.blogspot.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-02 : 14:02:46

I have no idea how to parse the data at .(dot) place?
for that you can use a string parsing function like below


SELECT Val FROM dbo.ParseValues('1050 Buckingham Street Watertown C S06795660 . 1050 Buckingham Street Watertown C S06795660 . 7375Â McKNIGHT RD PITTSBURGH P S15237351 . 7375Â McKNIGHT RD PITTSBURGH P S15237351 . 7375Â McKNIGHT RD PITTSBURGH P S15237351','.')

this will spilt individual addresse values based on . and then you will get a table of addresses as output

The code can be found in below link

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-10-02 : 21:57:03

If splitting of the string based on '.' as your delimiter then I would suggest you to make use of the XML trick explained here
http://vadivel.blogspot.com/2011/10/how-to-split-delimited-string-values-in.html

Usage would be as simple as this:

SELECT parsedValue FROM [dbo].[SplitUsingXML]
('1050 Buckingham Street Watertown C S06795660 . 1050 Buckingham Street Watertown C S06795660 . 7375Â McKNIGHT RD PITTSBURGH P S15237351 . 7375Â McKNIGHT RD PITTSBURGH P S15237351 . 7375Â McKNIGHT RD PITTSBURGH P S15237351','.')

Best Regards
Vadivel

http://vadivel.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources