How to capture ('abc','def') as a string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to capture ('abc','def') as a string

Author

Topic

pdiebels
Starting Member

2 Posts

Posted - 2010-12-06 : 16:40:45

I have an application (Business Objects) passing a string to SQL Server 2005 as part of a query. I want to capture/manipulate the string prior to executing the string. The passed string (for example) is: ('abc','def')
It may come with 1 to many operands: ('abc','def','ghi'). The entire query might be:

Select field1, ('abc','def') from Table1

I want to wrap something around (I can't insert anything in the middle of it, but have access to both sides of it) this string so that I can have it as a single string to manipulate in a valid query, such as:

Select field1, MAKE_FIELD[('abc','def')] from Table1.

The goal is to reduce the string to: abc,def (or abc,def,ghi). The string manipulation I can do, but I am unable to capture it into a single variable. Any ideas?

pdiebels

edit: moved to correct forum

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-06 : 18:52:41

So it is passing a FIELD LIST? or are you passing criteria to limit the desired results?

Poor planning on your part does not constitute an emergency on my part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-07 : 03:28:19

Select 'abc','def'
Select '''abc'',''def'''

Madhivanan

Failing to plan is Planning to fail

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-12-07 : 04:46:49

I'm a little confused...the ('abc','def'), are those column names in a table and you want to query for the data in these columns or is it a fixed string that needs to be a part of the record set?

DECLARE @string varchar(50) = '(''abc'',''def'')'
DECLARE @sql nvarchar(500)

PRINT @string

SET @sql = 'SELECT Col1, ' + REPLACE(REPLACE(REPLACE(@string, ')', ''), '(', ''), '''', '') + ' FROM table'

EXEC sp_executesql @SQL

If this is what you want to do then be VERY careful about sql injection -> http://www.sommarskog.se/dynamic_sql.html

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

pdiebels
Starting Member

2 Posts

Posted - 2010-12-07 : 11:19:22

Here is some more context. In the Business Objects application, users can input 1 to many values for selection criteria for a report. Business Objects constructs a query from that and sends that to SQL Server. The results come back and are presented thru a reporting tool. So, for a more meaningful example, if a user was selecting customers by state, they might input AK,OK in the Bus. Obj. tool. As a developer, I specify the fields I want = CUST_NAME, CITY, STATE. The query passed to SQL Server becomes:

SELECT CUST_NAME, CITY, STATE FROM CUSTOMER_TABLE WHERE STATE IN ('AK','OK').

Bus. Obj. has a function (called @variable) that allows the developer to capture the user input so that it can also be presented on the report (what the user asked for). To use, I add it as a field I want = CUST_NAME, CITY, STATE, @VARIABLE. The tool substitutes the user input for @VARIABLE and the query becomes:

SELECT CUST_NAME, CITY, STATE, ('AK','OK') FROM CUSTOMER_TABLE WHERE STATE IN ('AK','OK')

Note that @variable is a place-holder, not a variable itself. That is why I can't directly use double-tic marks. Lumbago's solution will work, but I first need to do something to get the string into a variable (what his DECLARE @string is doing). I can send the query with anything wrapped around @VARIABLE, but I can't directly modify the contents. So, I can say the fields I want are CUST_NAME, CITY, STATE, some_func(@VARIABLE) and the query passed becomes:

SELECT CUST_NAME, CITY, STATE, some_func(('AK','OK')) FROM CUSTOMER_TABLE WHERE STATE IN ('AK','OK')

At it's simplest then, I think the question becomes is there anything I can supply for xxx and/or yyy (including User Defined Functions) in

SELECT xxx('AK','OK')yyy

that would return ('AK', 'OK') as the result?

pdiebels

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources