format string - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

format string

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-01-18 : 08:51:24

how is it possible to place an apostrophe around each word when they are separated by comma
i.e.
@values is a dynamic variable which can change...
declare @values varchar(1000)
set @values = hello, test, where, query
to become
@values = 'hello', 'test', 'where', 'query'

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-18 : 09:12:38

interesting.

Here's one horrible way.


--@values is a dynamic variable which can change...
declare @values varchar(1000)
set @values = 'hello, test, where   ,    query'
--to become
--@values = 'hello', 'test', 'where', 'query'

SELECT @values

WHILE @values LIKE '%[ ],%' OR @values LIKE '%,[ ]%' SET @values = REPLACE(REPLACE(@values, ' ,', ','), ', ', ',')

SELECT '''' + REPLACE(@values, ',', ''', ''') + ''''

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-18 : 09:13:59

edit -- there are lots of edge cases it will screw up on.

This doesn't sound like something you want to do in the db anyway!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-01-18 : 09:18:44

Hi,
Yes, I have to do this in DB.
But this does NOT seem to work.
Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-18 : 09:46:10

huh?
This is the output:


-------------------------------------
hello, test, where   ,    query

(1 row(s) affected)
-------------------------------------
'hello', 'test', 'where', 'query'

(1 row(s) affected)

So I can't see what the problem is -- it answers you initial question.

Care to say *why* is does NOT seem to work?
an error......
a crumb....
a clue?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-01-18 : 10:16:54

I was referring to what you mentioned which was "there are lots of edge cases it will screw up on.
"
Thanks

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-18 : 10:31:56

Hello arkiboys,

How about something like this?

DECLARE @values VARCHAR(100), @newValues VARCHAR(100)=''
SELECT @values = 'hello, test, where, query'

;with cte AS (
    SELECT 
        ROW_NUMBER() OVER(ORDER BY p1.Number) AS sr,
        number
    FROM master..spt_values p1
    WHERE p1.type = 'p' 
        AND p1.number between 1 AND LEN(@values) + 2
    AND SUBSTRING(',' + @values + ',',p1.number, 1) = ',' 
)

SELECT @newValues = + @newValues + '''' + LTRIM(SUBSTRING(' ' + @values, a.number + 1, b.number - a.number - 1)) + ''','
FROM cte a
INNER JOIN cte b ON b.sr = a.sr + 1

SELECT LEFT(@newValues, LEN(@newValues)-1)

EDITED: resolve misplaced formatting...

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-18 : 10:43:02

quote:
Originally posted by robvolk

If all you're doing is putting double quotes around comma-separated elements:

SET @Values='"' + REPLACE(@Value, ',', '","') + '"'

Absolutely no need to split this into rows, much less using a cursor.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-18 : 10:56:23

quote:
Originally posted by arkiboys

I was referring to what you mentioned which was "there are lots of edge cases it will screw up on.
"
Thanks

OK Dude. I'll try one more time......

What are the edge cases that are relevant for you?

What are your *exact* rules? do we have to ignore commas inside double quotes. Do we have to deal with null values? do we have leading or trailing commas that need to be excluded.

Basically. Give a little more information and you'll get a lot more help.

Also I'll raise it one more time......

WHY DO THIS IN THE DB? it isn't good at it and I don't understand WHY you need to do it in the first place...

You posted another post which shows you already know about string splitting functions. If you know about them then why not just return data to your app in that format then wrap the values in quotes in the display layer?

More importantly though -- why post a question and then refuse to explain why the answer doesn't fit?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-01-18 : 11:42:33

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources