CODEWITHSUNDEEP
sqlsql-serverstringt-sqldate
Alekhya varma
Alekhya varma

Reputation: 95

Remove single quotes in one of the table column in SQl server

I am trying to implement query to remove the single quotations existing in few rows in the entire column.

I have tried the following queries to try to implement it, but no luck. Please do help me.

SELECT CASE WHEN RIGHT(saleDate,1) = '"' AND LEFT(saleDate,1) = '"' THEN REVERSE(STUFF(REVERSE(STUFF(saleDate, 1, 1, '')), 1, 1, '')) 
       WHEN RIGHT(saleDate,1) = '"' THEN REVERSE(STUFF(REVERSE(saleDate), 1, 1, '')) 
       WHEN LEFT(saleDate,1) = '"' THEN STUFF(saleDate, 1, 1, '')
       ELSE saleDate END as saleDate
FROM [Staging].[Target_ActivationFlowPrepaidAct];

SET saleDate = REPLACE(saleDate,'"', '')

SELECT TRIM(leading '' FROM saleDate) FROM [Staging].[Target_ActivationFlowPrepaidAct]

Data - 

saleDate
2020/04/20 12:42:05
2020/04/22 11:35:58
2020/04/24 08:10:50
2020/04/25 10:06:59
'2018-11-29 12:47:23'
'2018-12-04 18:36:08'
'2018-12-10 11:33:09'
'2018-12-15 12:33:08'

output - 

saleDate
2020/04/20 12:42:05
2020/04/22 11:35:58
2020/04/24 08:10:50
2020/04/25 10:06:59
2018-11-29 12:47:23
2018-12-04 18:36:08
2018-12-10 11:33:09
2018-12-15 12:33:08

Upvotes: 0

Views: 4630

Answers (2)

GMB
GMB

Reputation: 222582

Don't store dates as strings. Instead, you should be using a date-like datatype... and wouldn't need to ask this question.

That said, if you want to remove leading and trailing single quotes, you can use TRIM():

SELECT TRIM('''' from saledate) orderdate
FROM [Staging].[Target_ActivationFlowPrepaidAct];

Note that the single quote needs to be escaped with an additional single quote.

The upside of this approach is that it only removes single quotes at the beginning and end of the string, not possible embedded strings. While this does not seem to make a difference for your use case, this might be important in more complex situations.

Upvotes: 0

Ilyes
Ilyes

Reputation: 14928

I suppose you're just looking for REPLACE()

CREATE View MyData AS
SELECT
'2020/04/20 12:42:05' Value
UNION ALL SELECT '2020/04/22 11:35:58'
UNION ALL SELECT '2020/04/24 08:10:50'
UNION ALL SELECT '2020/04/25 10:06:59'
UNION ALL SELECT '2018-11-29 12:47:23'
UNION ALL SELECT '''2018-12-04 18:36:08'''
UNION ALL SELECT '''2018-12-10 11:33:09'''
UNION ALL SELECT '''2018-12-15 12:33:08''';

SELECT Value, REPLACE(Value, '''', '') OutputValue
FROM MyData;

Here is a db<>fiddle

Upvotes: 2

Related Questions