Insert Value From Column into String Value of Another Column: Query

Question

Insert Value From Column into String Value of Another Column: Query

jay.schwinn

Valued Member

Points: 61
More actions
January 8, 2013 at 11:15 am

#393903

Hello,
This is my first post to this forum. Thanks in advance to anyone who takes the time to study this question. I am using Microsoft SQL Server Management Studio 2008.
I want to run a query to insert a value from column groupId (data type int) into a string value for column layerXMLData (data type text). Because layerXMLData contains values for multiple polygon shapes, I'd like to be able to insert the groupId multiple times depending on the number of shapes. Also, since the data types are different, I'm betting on needing a CAST or CONVERT function in the statement.
For a one row example:
groupId = 206
layerXMLData = <polygon points='-104.80,31.57 -104.80,31.59 -104.79,31.59 -104.79,31.57 ' id='MT-209' /><polygon points='-104.79,31.59 -104.79,31.60 -104.77,31.60 -104.77,31.59 ' id='MT-209' />
In this case, there are two shapes, so I'd like to insert groupId as a value two times. The shapes are bound by the characters: < />
(irrelevant to the question at hand, but FYI, I limited the decimal places for the points for simplicity's sake)
The end result would look something like:
layerXMLData = <polygon points='-104.80,31.57 -104.80,31.59 -104.79,31.59 -104.79,31.57 ' id='MT-209' groupid='206'/><polygon points='-104.79,31.59 -104.79,31.60 -104.77,31.60 -104.77,31.59 ' id='MT-209' groupid='206'/>
Any pointers (or solutions(!)) out there for a rookie SQL user?
Thanks!
J.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

Hi and welcome to SSC. In order to provide any help you need to first provide enough details so we know where to begin. You will need to post ddl (create table statements), sample data (insert statements) and desired output based on your sample data. It takes a little more effort on your part but you will be rewarded with fast answers that are tested and accurate. Please see the first link in my signature for details about best practices when posting questions.

_______________________________________________________________

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/

Dwain Camps SSC Guru Points: 86908 More actions · Answer 2

How about something simple like this?

DECLARE @layerXMLData VARCHAR(8000) =

'<polygon points=''-104.80,31.57 -104.80,31.59 -104.79,31.59 -104.79,31.57 '' id=''MT-209'' />

<polygon points=''-104.79,31.59 -104.79,31.60 -104.77,31.60 -104.77,31.59 '' id=''MT-209'' />'

SELECT REPLACE(@layerXMLData, '/>', 'groupid=''206''/>')

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

jay.schwinn Valued Member Points: 61 More actions · Answer 3

Sean,

Thanks for the pointers on correct posting. Apologies for my rookie mistake.

I have been using and reviewing your how-to-post document and I've learned a lot just from reading it. I was stuck for a while after using the SELECT QUOTENAME to get my existing data into the statement. The INSERT INTO failed, and I finally figured out it was because of the apostrophes in my text field; I had to change them to double apostrophes.

So, here is my statement, which I have successfully run:

--===== If the test table already exists, drop it

IF OBJECT_ID('[LOCAL_TEST].[dbo].[testtable10]','U') IS NOT NULL

DROP TABLE [LOCAL_TEST].[dbo].[testtable10]

--===== Create the test table with

CREATE TABLE [LOCAL_TEST].[dbo].[testtable10]

(

groupId INT IDENTITY PRIMARY KEY,

layerXMLData TEXT

)

--===== All Inserts into the IDENTITY column

SET IDENTITY_INSERT [LOCAL_TEST].[dbo].[testtable10] ON

--===== Insert the test data into the test table

INSERT INTO [LOCAL_TEST].[dbo].[testtable10]

(groupId, layerXMLData)

SELECT '206','<polygon points=''-104.09,31.79 -104.10,31.79 -104.10,31.80 -104.10,31.81 -104.0,31.8 '' id=''MT-207'' /><polygon points=''-104.07,31.81 -104.09,31.81 -104.091,31.82 -104.07,31.82 '' id=''MT-207'' />' UNION ALL

SELECT '207','<polygon points=''-104.15,31.75 -104.15,31.75 -104.13,31.75 -104.13,31.75 '' id=''MT-208'' /><polygon points=''-104.15,31.75 -104.13,31.75 -104.13,31.74 -104.15,31.74 '' id=''MT-208'' /><polygon points=''-104.14,31.75 -104.14,31.75 -104.14,31.75 -104.14,31.75 '' id=''MT-208'' />' UNION ALL

SELECT '208','<polygon points=''-104.80,31.57 -104.80,31.59 -104.79,31.59 -104.797,31.575 '' id=''MT-209'' /><polygon points=''-104.79,31.59 -104.79,31.60 -104.77,31.6 -104.77,31.59 '' id=''MT-209'' /><polygon points=''-104.79,31.56 -104.79,31.57 -104.77,31.57 -104.77,31.56 '' id=''MT-209'' /><polygon points=''-104.82,31.57 -104.81,31.57 -104.80,31.57 -104.80,31.56 -104.81,31.56 -104.82,31.56 '' id=''MT--100'' />'

--===== Set the identity insert back to normal

SET IDENTITY_INSERT [LOCAL_TEST].[dbo].[testtable10] OFF

My desired output would be to insert the label groupId and the value of groupId into the layerXMLData statement for every group of polygon points.

e.g.:

Thanks for the training and for your time.

Sean Lange (1/8/2013)
Hi and welcome to SSC. In order to provide any help you need to first provide enough details so we know where to begin. You will need to post ddl (create table statements), sample data (insert statements) and desired output based on your sample data. It takes a little more effort on your part but you will be rewarded with fast answers that are tested and accurate. Please see the first link in my signature for details about best practices when posting questions.

Dwain Camps SSC Guru Points: 86908 More actions · Answer 4

Have you tried the solution I suggested on your posted test data?

All you need to do is change the hard-coded 206 in the replacement with:

CAST(groupid AS VARCHAR(5))

and I think it will work.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

jay.schwinn Valued Member Points: 61 More actions · Answer 5

Dwain.c,

Thank you for your input and advice. This is indeed how I would like the final output to look. However, I need the groupId to populate automatically based on the column value of groupId

For instance, in this case, the groupId is indeed 206, but for the next record it could be 207, or 999, or anything.

My desired results should be a little clearer from the previous post I made to Sean.

dwain.c (1/8/2013)
How about something simple like this?
DECLARE @layerXMLData VARCHAR(8000) =
'<polygon points=''-104.80,31.57 -104.80,31.59 -104.79,31.59 -104.79,31.57 '' id=''MT-209'' />
<polygon points=''-104.79,31.59 -104.79,31.60 -104.77,31.60 -104.77,31.59 '' id=''MT-209'' />'
SELECT REPLACE(@layerXMLData, '/>', 'groupid=''206''/>')

Dwain Camps SSC Guru Points: 86908 More actions · Answer 6

jay.schwinn (1/10/2013)
Dwain.c,
Thank you for your input and advice. This is indeed how I would like the final output to look. However, I need the groupId to populate automatically based on the column value of groupId
For instance, in this case, the groupId is indeed 206, but for the next record it could be 207, or 999, or anything.
My desired results should be a little clearer from the previous post I made to Sean.
dwain.c (1/8/2013)
How about something simple like this?
DECLARE @layerXMLData VARCHAR(8000) =
'<polygon points=''-104.80,31.57 -104.80,31.59 -104.79,31.59 -104.79,31.57 '' id=''MT-209'' />
<polygon points=''-104.79,31.59 -104.79,31.60 -104.77,31.60 -104.77,31.59 '' id=''MT-209'' />'
SELECT REPLACE(@layerXMLData, '/>', 'groupid=''206''/>')

Are you saying this doesn't work?

SELECT REPLACE(layerXMLData, '/>', 'groupid=''' + CAST(groupid AS VARCHAR(5))+ '''/>')

FROM [LOCAL_TEST].[dbo].[testtable10]

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

jay.schwinn Valued Member Points: 61 More actions · Answer 7

Dwain,

It looks like my last post #1405712 was posted less than a minute after your new suggestion, so that post was actually a reply to your first comment, not your new suggestion.

I tried your statement and got

"Msg 8116, Level 16, State 1, Line 1

Argument data type text is invalid for argument 1 of replace function."

Thanks!

Are you saying this doesn't work?
SELECT REPLACE(layerXMLData, '/>', 'groupid=''' + CAST(groupid AS VARCHAR(5))+ '''/>')
FROM [LOCAL_TEST].[dbo].[testtable10]

Dwain Camps SSC Guru Points: 86908 More actions · Answer 8

jay.schwinn (1/11/2013)
Dwain,
It looks like my last post #1405712 was posted less than a minute after your new suggestion, so that post was actually a reply to your first comment, not your new suggestion.
I tried your statement and got
"Msg 8116, Level 16, State 1, Line 1
Argument data type text is invalid for argument 1 of replace function."
Thanks!
Are you saying this doesn't work?
SELECT REPLACE(layerXMLData, '/>', 'groupid=''' + CAST(groupid AS VARCHAR(5))+ '''/>')
FROM [LOCAL_TEST].[dbo].[testtable10]

I don't work much with TEXT columns because they're due to be deprecated in future SQL releases but you might try putting a cast around layerXMLData as:

CAST(layerXMLData AS VARCHAR(MAX))

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

jay.schwinn Valued Member Points: 61 More actions · Answer 9

It works! Thanks so much for sticking with me and solving this issue.

The complete string is:

SELECT REPLACE(CAST(layerXMLData AS VARCHAR(MAX)), '/>', 'groupid=''' + CAST(groupid AS VARCHAR(5))+ ''' />')

FROM [LOCAL_TEST].[dbo].[testtable10]

Are you saying this doesn't work?
SELECT REPLACE(layerXMLData, '/>', 'groupid=''' + CAST(groupid AS VARCHAR(5))+ '''/>')
FROM [LOCAL_TEST].[dbo].[testtable10]

[/quote]

I don't work much with TEXT columns because they're due to be deprecated in future SQL releases but you might try putting a cast around layerXMLData as:

CAST(layerXMLData AS VARCHAR(MAX))[/quote]

Dwain Camps SSC Guru Points: 86908 More actions · Answer 10

Happy to be of service my friend.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St