asked on 2/1/2019

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

Microsoft SQL Server SQL

Last Comment

mountiers

2/1/2019

ASKER CERTIFIED SOLUTION

Scott Pletcher

2/1/2019

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

mountiers

2/1/2019

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

Scott Pletcher

2/1/2019

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.

mountiers

2/1/2019

ASKER

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

Scott Pletcher

2/1/2019

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')

mountiers

2/1/2019

ASKER

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 ;-)

mountiers

2/1/2019

ASKER

Thanks for all your guidance