Replacing string values

Question

Post reply

Replacing string values

stricknyn

SSCertifiable

Points: 6560
More actions
December 19, 2014 at 11:00 pm

#299585

Hello all,
So I have the following column named String in a table:
<key>Children</key><integer>2</integer>
This of course can vary for the different records. What's the best way to replace the 2 with the contents of my variable with TSQL?
declare @children int
set @children = 4
I want to do something like this:
SELECT <key>Children</key><integer>@children</integer>
Thanks,
Strick

Viewing 5 posts - 1 through 4 (of 4 total)

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

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 1

Quick simple suggestion

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @SAMPLE_DATA TABLE(TSTR VARCHAR(100) NOT NULL);

INSERT INTO @SAMPLE_DATA(TSTR)

VALUES ('<key>Children</key><integer>2</integer>')

,('<key>Children</key><integer>123</integer>')

,('<key>Children</key><integer></integer>')

;

DECLARE @children INT = 4;

SELECT

SD.TSTR

,SUBSTRING(SD.TSTR,1,(CHARINDEX('<integer>',SD.TSTR,1) + LEN('<integer>') - 1 ))

+ CONVERT(VARCHAR(12),@children,0)

+ SUBSTRING(SD.TSTR,CHARINDEX('</integer>',SD.TSTR,1),LEN(SD.TSTR)) AS OUT_STR

FROM @SAMPLE_DATA SD;

Results

TSTR OUT_STR

------------------------------------------ ----------------------------------------

<key>Children</key><integer>2</integer> <key>Children</key><integer>4</integer>

<key>Children</key><integer>123</integer> <key>Children</key><integer>4</integer>

<key>Children</key><integer></integer> <key>Children</key><integer>4</integer>

ScottPletcher SSC Guru Points: 101120 More actions · Answer 2

Here's an alternative:

declare @children int

set @children = 4

SELECT

string,

STUFF(string, start_of_integer_value, CHARINDEX('<', string, start_of_integer_value) - start_of_integer_value,

CAST(@children AS varchar(10)))

FROM (

SELECT '<key>Children</key><integer>2</integer>' AS string UNION ALL

SELECT '<key>Children</key><integer>123</integer>' UNION ALL

SELECT '<key>Children</key><integer></integer>'

) AS test_data

CROSS APPLY (

SELECT CHARINDEX('<integer>', string) + 9 AS start_of_integer_value

) AS assign_alias_name

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Heh... a better way to do it would be to get that nasty ol' denormalized junk known as XML out of your database and replace it with proper columns and data. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

stricknyn SSCertifiable Points: 6560 More actions · Answer 4

Thanks everyone for your responses. This helped.

Strick