Replace Data in a String Based on Data in Table In line Query

Question

Post reply

Replace Data in a String Based on Data in Table In line Query

sudhirnune

SSCrazy

Points: 2342
More actions
April 10, 2013 at 11:29 pm

#276270

Hi All,
I trying to update a String based on the Inline View.
String:
-1 * [SAL] - [BON] - [COMM]
In Line Query:
Select * from TABLE
Values ( SAL , COMM ) in table
New String (Updated):
-1 * [SAL] - 0 - [COMM]
Please need help.

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

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

sudhirnune SSCrazy Points: 2342 More actions · Answer 1

sudhirnune

SSCrazy

Points: 2342

April 11, 2013 at 9:39 am

#1605664

Need to Solve this Query issue ??? 🙁

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Please read and follow the instructions regarding what to post and how to do it: http://www.sqlservercentral.com/articles/Best+Practices/61537/.

Sorry, but you just haven't provided enough information for anyone to really help.

sudhirnune SSCrazy Points: 2342 More actions · Answer 3

When we get the Following String as In put

-1 * [SAL] - [BON] - [COMM]

need to check for the Data from the Below Table.

Columns:

--------

SAL

COMM

Once you search through the table above, the missing columns in the string should be replaced by "0"

So the output can be:

-1 * [SAL] - 0 - [COMM]

all the Data need to be searched will be enclosed between "[ & ]".

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

sudhirnune (4/11/2013)
When we get the Following String as In put
-1 * [SAL] - [BON] - [COMM]
need to check for the Data from the Below Table.
Columns:
--------
SAL
COMM
Once you search through the table above, the missing columns in the string should be replaced by "0"
So the output can be:
-1 * [SAL] - 0 - [COMM]
all the Data need to be searched will be enclosed between "[ & ]".

Just as helpful as your original post. Again, please read the article I referenced in my previous post.

sudhirnune SSCrazy Points: 2342 More actions · Answer 5

Below is the Query Solved the Problem:

declare @string varchar(500)

set @string = '-1 * [ALL_1] + [BAL] - [SAL]'

declare @pos int

declare @piece varchar(500)

declare @EXIS_VALUE VARCHAR(100)

declare @string_new varchar(500)

--Need to tack a delimiter onto the end of the input string if one doesn’t exist

if right(rtrim(@string),1) <> '['

set @string_new = @string

set @string = @string + '['

Set @string = REPLACE(@string,' ','')

set @pos = CHARINDEX('[' , @string)

while @pos <> 0

begin

set @piece = left(@string, @pos - 1)

SET @piece = REPLACE(@piece,']','')

set @piece = REPLACE(@piece,'-','')

set @piece = REPLACE(@piece,'+','')

-- You have a piece of data, so insert it, print it, do whatever you want to with it.

SELECT @EXIS_VALUE=PAYMENT_TYPE from TEST_DATA_SRC WHere PAYMENT_TYPE in (@piece);

IF @EXIS_VALUE IS NULL

set @string_new = REPLACE(@string_new,'['+@piece+']',0)

-- SET @piece = 0

print cast(@piece as varchar(500))

Print cast(@string_new as varchar(500))

set @string = stuff(@string, 1, @pos, '')

set @pos = CHARINDEX('[' , @string)

Set @EXIS_VALUE = NULL

end

print ''

Print 'FINAL_STRING:'+cast(@string_new as varchar(500))