Add quotes around every value
-
Hi guys,
I have this output (abc,xyz,kty).
How can i add quotes around every value?
Thank you
-
Have a look at the QUOTENAME function. Something like this
Select QUOTENAME ('Markus','"')
Markus
[font="Verdana"]Markus Bohse[/font]
-
Thank you, but in my case i don't have a string as the original values.
I tried and got Invalid column name error
-
you have to place the values within quotes i hope you have omitted the quotes inthe quotename command.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com - Thank you, but in my case i don't have a string as the original values.It doesn't matter if you original value is a string or numeric. Here are 2 examples for the northwind db:use northwind
Select quotename(ContactName,'"') from customers
Select quotename(Orderid,'"') from orders
Markus[font="Verdana"]Markus Bohse[/font]
-
Just a suggestion...
When someone gives you an example... don't just take it at 100% face value... it's just an example. When someone suggests something new to you, like QUOTENAME, take a little time to look it up in Books Online and see what it really does. Learning something new does takes a little effort on your part...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
If you think i don't research then you are mistaken.I don't take anything for granted.
It doesn't work in my case because the values are not coming from the table , they are passing as a variable in the stored procedure. But anyway thanks for your help, no more questions.
-
I'd like to see your final solution if you don't mind sharing it with the world. It may help someone else in the near future who is in the same situation as you are (were).
-
Sure, this is what i used:
declare @List VARCHAR(100)
set @List = 'abc,xyz,kty'
set @List = '''' + REPLACE(REPLACE(@List, '''', ''''''), ',', ''',''') + ''''
print @List
-
> I have this output (abc,xyz,kty).
Can you tell from where?
You know, it's too late to apply quotation when the string is built.
If any value has a comma in it you gonna split it and lose it if you do quotation the way you did it.
_____________
Code for TallyGenerator -
Serqiy is correct... is the stuff you putting into @List available in a table anywhere? If so, we can resolve the whole table instead of using RBAR methods...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Those values passed as a string (user types those values comma separated inside the text box) from application and then i suppose to search the database for the records passed.
-
So, why you need quotes?
_____________
Code for TallyGenerator -
So why don't you convert the list of values from your application into a table and then use this through an inner join?
See http://databases.aspfaq.com/database/how-do-i-simulate-an-array-inside-a-stored-procedure.html for details - my preference would be to use a function to create the table of values from the list:
CREATE FUNCTION dbo.FAQ_CommaSeparatedListToSingleColumn
(
@cslist VARCHAR(8000)
)
RETURNS @t TABLE
(
Item VARCHAR(64)
)
BEGIN
DECLARE @spot SMALLINT, @STR VARCHAR(8000), @sql VARCHAR(8000)
WHILE @cslist <> ''
BEGIN
SET @spot = CHARINDEX(',', @cslist)
IF @spot>0
BEGIN
SET @STR = LEFT(@cslist, @spot-1)
SET @cslist = RIGHT(@cslist, LEN(@cslist)-@spot)
END
ELSE
BEGIN
SET @STR = @cslist
SET @cslist = ''
END
INSERT @t SELECT @STR
END
RETURN
END
GO
J
-
Would have been a bit helpful to know that up front.
First, whatever you do... do NOT use dynamic SQL for this because you are allowing the user to type whatever they want and you will surely be made to suffer from an SQL Injection attack.
If you'd like, post the code you ended up with in your stored procedure and let us take a look both from a security aspect and a performance aspect.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply