CODEWITHSUNDEEP
sqlsql-serverselect
Andy
Andy

Reputation: 383

SQL Server SELECT To Return String Including Single Quotes

I need to get a formatted string with data from a SELECT statement. The problem is that I need it to return with single quotes. Here's an example.

I have a table that contains 2 columns TrainingSwipeID (int) and ExtendedDate (datetime). For an example I have a row in the table that contains

TrainingSwipeID = 123

TrainingEnd = 04/23/2019 09:00:00

I need to create a SELECT statement that will return the formatted string such as {'TrainingSwipeID':123,'TrainingEnd ':04/23/2019 09:00:00}

I've researched and found that you can double single quote to get around this issue. I've tried the following with no luck and I get the following error "Conversion failed when converting the varchar value '{'TrainingSwipeID':' to data type int."

SELECT '{''TrainingSwipeID'':' + TrainingSwipeID + '''TrainingEnd'':' + TrainingEnd + '}' 
AS MyFormattedString 
FROM TrainingSwipe

Can anyone help?

Upvotes: 0

Views: 2056

Answers (2)

Andy
Andy

Reputation: 383

I finally used the simplest answer from @AlexKudryashev but Luis' answer worked as well. 

SELECT CONCAT('{''TrainingSwipeID'':' , TrainingSwipeID, ',''TrainingEnd'':', TrainingEnd, '}')

Upvotes: 0

Luis Cazares
Luis Cazares

Reputation: 3585

The numeric and date/time data types have a higher precedence than the string data types. That's why you need to convert the numeric types into strings and prevent undesired implicit conversions.

SELECT '{''TrainingSwipeID'':' + CAST(TrainingSwipeID AS varchar(15)) 
      + '''TrainingEnd'':' + CONVERT( varchar(20), TrainingEnd , 101) + ' ' 
      + CONVERT( varchar(20), TrainingEnd , 8) + '}' 
AS MyFormattedString 
FROM TrainingSwipe

Upvotes: 2

Related Questions