mountiers
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|| '''|'''||c ol4....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
I'm trying to do in sql server something that was very easy in Oracle Sql...
select col1||'''|'''||col2||'''|'
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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
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')
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)
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 ;-)
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 ;-)
ASKER
Thanks for all your guidance
ASKER
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,tim
but if I leave out the timestamp field it works fine!!!
Any ideas on this part?
Stephen