Extract Data From A String in SQL Server

Question

Extract Data From A String in SQL Server

sudhirnune

SSCrazy

Points: 2342
More actions
April 11, 2013 at 11:16 pm

#273328

Hi All,
I have a String as in put, I need to Extract the Data in teh String.
Delimiter for the String is "["
String: -1 * [SAL] + [COMM] - [BON]
Please need help...

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

sudhirnune (4/11/2013)
Hi All,
I have a String as in put, I need to Extract the Data in teh String.
Delimiter for the String is "["
String: -1 * [SAL] + [COMM] - [BON]
Please need help...

This article[/url] by Jeff Moden et al describes the evolution of an excellent TSQL delimited string splitter.

I suspect you are attempting to resolve the column names from this string. Can you post the ddl and a few rows of sample data of the table containing the strings?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sudhirnune SSCrazy Points: 2342 More actions · Answer 2

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

sudhirnune (4/12/2013)
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))

Same question as this? http://www.sqlservercentral.com/Forums/FindPost1441612.aspx

Are you able to answer this question:

http://www.sqlservercentral.com/Forums/FindPost1440672.aspx

It's significant - as far as I can tell, the stored procedure will return multiple result sets each with a different bunch of columns, all unknown until runtime.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden