SQL remove character from string

Question

I am subtracting the date part from a string; sometimes there is an extra character at the end like here:

Mon,  1 Oct 2011 10:51:52 -0400%

Sometimes, the date is fine and does not have to be trimmed:

Mon,  21 Oct 2011 10:51:52 -0400

How do I remove this last character ( % in this example) in the case that the character does exist?

what column type are your dates being stored in? I don't think you would have this issue if it were stored in a datetime — Kritner
– Kritner, Commented Aug 24, 2015 at 17:27

Jason W · Accepted Answer · 2015-08-24 17:22:36Z

1

You can use REPLACE if you know the character.

REPLACE(@input, '%', '')

If you have a fixed format for everything before the additional character, you can use CHARINDEX to find the last ':', then get a SUBSTRING of the first character up to the known good characters (up to the -0400 in your case):

DECLARE @Input VARCHAR(50) = 'Mon,  1 Oct 2011 10:51:52 -0400%'
SELECT SUBSTRING(@Input, 1, LEN(@Input) - CHARINDEX(':', REVERSE(@Input)) + 9)

This returns:

Mon,  1 Oct 2011 10:51:52 -0400

edited Aug 24, 2015 at 17:22

answered Aug 24, 2015 at 17:17

Jason W

13.2k3 gold badges35 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL remove character from string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related