Post reply

Asterisk in varchar field

  • August 14, 2012 at 8:59 am

    #265085

    I'm attempting to store a string of SQL in a varchar field. I want to include the '*'. The purpose is to use that line for dynamic SQL later. My predecessor must have figured this out because she has it stored in the field currently and I just need to modify it.

    I've tried this:

    SELECT [ValidationRule]='[PERFORMINGPROVIDERID]=COALESCE(NULLIF([PERFORMINGPROVIDERID],''),NULLIF([PERFORMINGPROVIDERNPI],''),NULLIF([BILLINGPROVIDERNPI],''),REPLICATE('*', 10))'

    This produces error:

    Operand data type varchar is invalid for multiply operator.

    I've tried single quotes, double quotes, QUOTENAME, the ASCII code -- * . I can't get it. Any help is greatly appreciated.

  • August 14, 2012 at 9:05 am

    #1525581

    I don't see anything wrong there. Is that the entire select? Could you perhaps post the table structure and just one row of data (as an insert) so that I can test?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 14, 2012 at 9:08 am

    #1525584

    All your single quotes must be escaped by doubling them:

    SELECT [ValidationRule]

    = '[PERFORMINGPROVIDERID]=COALESCE(NULLIF([PERFORMINGPROVIDERID],''''),NULLIF([PERFORMINGPROVIDERNPI],''''),NULLIF([BILLINGPROVIDERNPI],''''),REPLICATE(''*'', 10))'

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • August 14, 2012 at 9:09 am

    #1525587

    I think what OP is looking for is double single-quotes as opposed to just single quotes or double quotes

    ''*''

    instead of

    '*'

    or

    "*"

    Produces the following result:

    [PERFORMINGPROVIDERID]=COALESCE(NULLIF([PERFORMINGPROVIDERID],'),NULLIF([PERFORMINGPROVIDERNPI],'),NULLIF([BILLINGPROVIDERNPI],'),REPLICATE('*', 10))

    Is that correct?

  • August 14, 2012 at 9:10 am

    #1525591

    Dang! Not only did Eugene beat me, he corrected the rest. His code is correct.

  • August 14, 2012 at 9:21 am

    #1525599

    Ah, didn't notice the initial quote.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 14, 2012 at 11:49 am

    #1525701

    Thank you all! I think part of my problem was Intellisense kept trying to add an additional single quote every time I tried to surround the single. So, if I put in '''' (4), it put '''''(5) and I didn't notice. I thought my problem was the asterisk and it was the single quotes all along. Blast it. Thanks again!

Viewing 7 posts - 1 through 6 (of 6 total)

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