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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Insert Value From Column into String Value of...

11 posts, Page 1 of 2

1 2 »»

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

Rate Topic

Display Mode

Topic Options

Author

Message

jay.schwinn

Posted Tuesday, January 08, 2013 11:15 AM

Forum 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

Sean Lange

Posted Tuesday, January 08, 2013 12:22 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 8,547, Visits: 8,204

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

Post #1404425

dwain.c

Posted Tuesday, January 08, 2013 6:37 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:42 AM
Points: 2,338, Visits: 3,158

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''/>')

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1404515

jay.schwinn

Posted Thursday, January 10, 2013 5:32 PM

Forum 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)

Post #1405707

dwain.c

Posted Thursday, January 10, 2013 5:36 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:42 AM
Points: 2,338, Visits: 3,158

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.

Post #1405710

jay.schwinn

Posted Thursday, January 10, 2013 5:37 PM

Forum 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

dwain.c

Posted Thursday, January 10, 2013 5:55 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:42 AM
Points: 2,338, Visits: 3,158

jay.schwinn (1/10/2013)

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]

Post #1405714

jay.schwinn

Posted Friday, January 11, 2013 2:59 PM

Forum 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

dwain.c

Posted Friday, January 11, 2013 6:57 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:42 AM
Points: 2,338, Visits: 3,158

jay.schwinn (1/11/2013)

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))

Post #1406292

jay.schwinn

Posted Tuesday, January 15, 2013 11:39 AM

Forum 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 »

11 posts, Page 1 of 2

1 2 »»

Permissions