Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Insert Value From Column into String Value of Another Column: Query Expand / Collapse
Author
Message
Posted Tuesday, January 8, 2013 11:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 8:53 AM
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.
Post #1404392
Posted Tuesday, January 8, 2013 12:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:05 PM
Points: 13,327, Visits: 12,820
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 Moden's 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)
Post #1404425
Posted Tuesday, January 8, 2013 6:37 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 3,438, Visits: 5,390
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!
Post #1404515
Posted Thursday, January 10, 2013 5:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 8:53 AM
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.
Post #1405707
Posted Thursday, January 10, 2013 5:36 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 3,438, Visits: 5,390
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!
Post #1405710
Posted Thursday, January 10, 2013 5:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 8:53 AM
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''/>')


Post #1405712
Posted Thursday, January 10, 2013 5:55 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 3,438, Visits: 5,390
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!
Post #1405714
Posted Friday, January 11, 2013 2:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 8:53 AM
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]


Post #1406279
Posted Friday, January 11, 2013 6:57 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 3,438, Visits: 5,390
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!
Post #1406292
Posted Tuesday, January 15, 2013 11:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 8:53 AM
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]
Post #1407403
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse