SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
jay.schwinn
jay.schwinn
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 25
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62565 Visits: 17959
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17809 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
jay.schwinn
jay.schwinn
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 25
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.:

<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' groupId='206' /><polygon points='-104.07,31.81 -104.09,31.81 -104.091,31.82 -104.07,31.82 ' id='MT-207' groupId='206' />

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

<polygon points='-104.80,31.57 -104.80,31.59 -104.79,31.59 -104.797,31.575 ' id='MT-209' groupId='208' /><polygon points='-104.79,31.59 -104.79,31.60 -104.77,31.6 -104.77,31.59 ' id='MT-209' groupId='208' /><polygon points='-104.79,31.56 -104.79,31.57 -104.77,31.57 -104.77,31.56 ' id='MT-209' groupId='208' /><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' groupId='208' />


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.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17809 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
jay.schwinn
jay.schwinn
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 25
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.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17809 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
jay.schwinn
jay.schwinn
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 25
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.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17809 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
jay.schwinn
jay.schwinn
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 25
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]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search