CODEWITHSUNDEEP
sqlsql-server
Nischey D
Nischey D

Reputation: 199

Variable declaration to store multiple values like full column value

I have an requirement where I have to store the column value into an declared variable is it possible anyways. I understand that an variable can store only single value and not multiple values. But in my case I need it badly.

What I have here is to set the column if date value which is in INT has to be converted into Date column and save it in that variable to compare it.

I have some column with values like below 

"A(B_CD_EE_FF_DFE)_ERT"

So i have to remove those two bracktes and process. 

Set @Variable= (SELECT  replace (replace ( replace (SUBSTRING(TXMLFileName, CHARINDEX('(', TXMLFileName)
, CHARINDEX(')',TXMLFileName) - CHARINDEX(')', reverse (TXMLFileName)) ) , ')', '') ,'(' ,'') ,'.xml' , '') 

from 
tblXML )

Gives me error and below gives answer as I select only top 1.

Set @Variable= (SELECT top 1  replace (replace ( replace (SUBSTRING(TXMLFileName, CHARINDEX('(', TXMLFileName)
, CHARINDEX(')',TXMLFileName) - CHARINDEX(')', reverse (TXMLFileName)) ) , ')', '') ,'(' ,'') ,'.xml' , '') 

from 
tblXML )

SO is there any known solution... ????? Thanks in advance

Upvotes: 0

Views: 809

Answers (2)

Thangadurai.B
Thangadurai.B

Reputation: 561

declare @tblXML table (TXMLFileName varchar(50))
insert into @tblXML values
('A(B_CD_EE_FF_DFE)_ERT'),
('(EE_FF_DFE)_ERT'),
('A(B_CD_EE)')

declare @tmp varchar(max)
SET @tmp = ''
SELECT @tmp=@tmp+ replace (replace ( replace (SUBSTRING(TXMLFileName, CHARINDEX('(', TXMLFileName)
, CHARINDEX(')',TXMLFileName) -1 ) , ')', '') ,'(' ,'') ,'.xml' , '')+''' '''
from 
@tblXML 

select ''''+@tmp

or you don't want to single row use this

SELECT ''''+ replace (replace ( replace (SUBSTRING(TXMLFileName, CHARINDEX('(', TXMLFileName)
, CHARINDEX(')',TXMLFileName) -1 ) , ')', '') ,'(' ,'') ,'.xml' , '')+''''
from 
@tblXML

Upvotes: 0

Wim Ombelets
Wim Ombelets

Reputation: 5265

A variable needn't just store a single value. You can create e.g. custom table types

CREATE TYPE MyCustomType AS TABLE (
    MyCustomTypeId INT,
    MyCustomValueColumn VARCHAR(100)
)

Or just declare a comparable table variable inline where needed.

Upvotes: 1

Related Questions