Link to home
Start Free TrialLog in
Avatar of mountiers
mountiersFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Issue converting sql concatenate (||) in Oracle sql to Sql Server (transact-sql )

Hi,

I'm trying to do in sql server something that was very easy in Oracle Sql...
select col1||'''|'''||col2||'''|'''||col3||'''|'''||col4....etc from <usertable>

and I'd end up with a string with columns separated by a pipe (|) it would cope fine with dates not needing cast or convert etc...

There doesn't seem to be the equivalent in transact-sql used within sql server.

I appreciate that I have to cast date columns but the implication is that I can use the + operator but when I do so I get the message  "Error converting varchar to numeric" implying it is trying to numerically add them together!!!
(e.g. select cast([timestamp] as varchar)+'|'+[No_]+'|'+ etc.... from <usertable> )

Any ideas what I'm doing wrong?
Thanks
Stephen
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mountiers

ASKER

Hi Scott,

Thanks so much for swift answer... I'm on SQL 2012 so that's easier!

I have been going around in circles with this... I was sure I tried this before...

Anyway this works fine for strings but when I include a timestamp column it puts out a null result for the whole string without an error message now...

e.g. select concat(convert(varchar,timestamp,23),'|',col2,'|',col3... from <usertable>

but if I leave out the timestamp field it works fine!!!

Any ideas on this part?

Stephen
You don't normally need to do converts yourself in a CONCAT.  NULL values work fine for me in the CONCAT.

But I don't think 23 is a valid format code in a CONVERT.  Try just removing the CONVERT.

Is timestamp a datetime type or is the data type timestamp?  A type of timestamp won't display correct without other conversions.  Any datetime should be fine.
Sorry I didn't make that clear ... It was the full row that ended up as null value when I added the timestamp datatype at the beginning.

Hmm.. yes it is a datatype of timestamp rather than datetime so are you saying these won't be able to be converted down to a character format?

Stephen
Yes, a timestamp format will not covert directly to varchar, it'll typically just be bunch of unprintable chars.

I played with the conversions some and I think I figured it out:
SELECT CONVERT(varchar(30), CAST(<timestamp_value> AS varbinary(30)), 2)

For example:

DECLARE @timestamp timestamp

SET @timestamp = 0x1bcd5

SELECT CONCAT(CONVERT(varchar(30), CAST(@timestamp AS varbinary(30)), 2), '|', 'A')
Ok just read up on timestamp datatype and now understand that these are not timestamps at all they are Hexadecimal values - I've changed it by  cast (timestamp as BIGINT) and it then comes out correctly as a number....

I notice down the table there is also a image datatype but fortunately they've not used this ALL are null.... Hopefully this will now run!!

I really appreciate your assistance ...I will give you maximum points!  

Stephen
BTW: 23 is valid it brings back format YYYY-MM-DD  - but obviously not for a timestamp datatype ;-)
Thanks for all your guidance