Post reply

how to put quotations to the field value

  • October 31, 2009 at 4:51 pm

    #96163

    hi every one,

    i have a table that is used for storing customer numbers of a user. a user may have multiple customers which was seperated by comma in the same field.

    ex:

    table:

    id int pk identity

    cno varchar(2000)

    Data will be like this in a table:

    id cno

    1 12,42

    2 123,123,123

    3 332,132,12,123,13

    i want to add quotations to the field values based on id only.

    declare @var varchar(300)

    select @var= cno from test where id=1;

    here i want to set @var='12','42' ( need to add quotations)

    can any give me some idea?

    Thanks

    Rock..

  • October 31, 2009 at 7:22 pm

    #1073543

    simple i think:

    ad a preceeding and ending single quote, and replace every comma with singlequote-comma-singlequote

    that will work weather there is one value or lots of values.

    SELECT '''' + REPLACE(YourColumn,',',''',''') + ''''

    from YourTable

    UPDATE YourTable

    Set YourColumn = '''' + REPLACE(YourColumn,',',''',''') + ''''

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 31, 2009 at 11:34 pm

    #1073557

    hi

    adding the quotation also, i can not able to retrieve values

    ex1:

    declare @var varchar(100)

    set @var=''

    select @var='''' + REPLACE(name,',',''',''') + ''''

    from test

    print @var

    select name from test1 where name in (@var);

    i am getting result as NULL.

    ex2:

    i have cross verified the values in t

    he variable and did small modification in the query to chk the o/p.

    data in the @var variable are 'emp','temp','dept'

    again i simple use the values in the query directly. here i am getting values

    select name from test1 where name in ('emp','temp','dept');

    now i am getting the required output.

    but why i am getting null values using ex1

    can any one guide me on this one.

    Thanks

    Rock....

  • November 1, 2009 at 5:07 am

    #1073574

    ahh, that's the difference between a variable and an array of values:

    SQL will not auto convert a string(@var) into an array of values...you have to explicitly do it.

    select name from test1 where name in (@var);

    --is the same as

    select name from test1 where name in (@ASingleStringThatHappensToContainAComma);

    to do what you want, you have to either use dynamic SQL, or you have to use one of the many Split() functions in the contributions section: here on SSC

    --dynamic sql

    DECLARE @sql varchar(500)

    SET sql = 'select name from test1 where name in (' + @var + ')'

    exec(@sql)

    --split

    select name from test1 where name in dbo.Split(@var,',')

    sql =

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 1, 2009 at 10:15 am

    #1073605

    i got it.

    Thank you very much Lowell

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

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