Asterisk in varchar field

Question

Asterisk in varchar field

lduvall

SSCarpal Tunnel

Points: 4869
More actions
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.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

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]

Scott D. Jacobson SSCertifiable Points: 6149 More actions · Answer 3

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?

Scott D. Jacobson SSCertifiable Points: 6149 More actions · Answer 4

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

lduvall SSCarpal Tunnel Points: 4869 More actions · Answer 6

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!