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
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
mountiers
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Blurred text
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
Avatar of mountiers
mountiers
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of mountiers
mountiers
Flag of United Kingdom of Great Britain and Northern Ireland image

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
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')
Avatar of mountiers
mountiers
Flag of United Kingdom of Great Britain and Northern Ireland image

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 ;-)
Avatar of mountiers
mountiers
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Thanks for all your guidance
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo