CAST or Convert

Question

CAST or Convert

TSQL Tryer

SSCrazy Eights

Points: 8869
More actions
October 8, 2014 at 9:32 am

#311651

Hi,
Have the following in my SELECT statement, which I'm having issues with when I modify it to include a CAST or a CONVERT.
CONCAT(PER.[PERSON-REF],ROW_NUMBER() OVER (PARTITION BY PER.[PERSON-REF] ORDER BY TEN.[tenancy-ref])) AS 'ID'
How do I convert or cast it to a varchar (20)?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Where do you want to include CAST or CONVERT?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

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

nto very clear what exactly you want to CAST or CONVERT, but lets assume it;'s a ROW_NUMBER...

CONCAT(PER.[PERSON-REF]

,CAST(ROW_NUMBER() OVER (PARTITION BY PER.[PERSON-REF]

ORDER BY TEN.[tenancy-ref]) AS VARCHAR(20)) AS 'ID'

_____________________________________________
"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]

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 3

I'm trying to cast/Convert the whole of the concatenation result. I take it you cant' do that?

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

TSQL Tryer (10/8/2014)
I'm trying to cast/Convert the whole of the concatenation result. I take it you cant' do that?

You can, but in your case it will fail before getting there anyway, as ROW_NUMBER needs to be converted to varchar first...

_____________________________________________
"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]

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 5

Thanks Eugene Elutin, what you've suggested has worked for me.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

You would need to convert the numeric to a string type in order to concatenate it. The concat accepts strings rather than numerics.

From BOL

CONCAT takes a variable number of string arguments

http://msdn.microsoft.com/en-us/library/hh231515.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

That's weird, I'm sure that integers would be implicitly converted into strings as shown on the examples from BOL.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

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

Luis Cazares (10/8/2014)
That's weird, I'm sure that integers would be implicitly converted into strings as shown on the examples from BOL.

You are absolutely right, CONCAT works without any conversion. So, my comment about error should be withdrawn.

Have not used CONCAT much myself...

So, have no idea what actually didn't work for OP.

May be it's due to some SET options?

_____________________________________________
"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]