'Replace last two characters in column

I have a column with dates in the format 201201, 201202, 201203 etc.

This is a financial database so there is a period 13; however periods 12 & 13 are combined for reporting purposes.

When the last two characters are 13, how do I replace them with 12?

I started off with

SELECT REPLACE(PERIOD, '13', '12')
FROM @b_s_summary

but, of course this messes with the year when it's 2013.

All advice gratefully received.

sql


Solution 1:[1]

You can use Substring in this case:

SELECT REPLACE(PERIOD, SUBSTRING(PERIOD, len(PERIOD) - 1, 2), '12')
FROM @b_s_summary WHERE PERIOD LIKE '%13'

Does that fit you, or do you need a more dynamic approach for past or upcoming years?

Solution 2:[2]

declare @Periods as Table ( Period VarChar(6) )
insert into @Periods ( Period ) values
  ( '201212' ), ( '201213' ), ( '201312' ), ( '201313' ) 

select Period, case
  when Right( Period, 2 ) = '13' then Substring( Period, 1, 4 ) + '12'
  else Period end as [AdjustedPeriod]
  from @Period

Solution 3:[3]

Likewise, RIGHT() could be used.

SELECT REPLACE(RIGHT(PERIOD,2),'13','12')

Solution 4:[4]

Try this:

select (case when right(period, 2) = '13' then left(period, 4) + '12' else period end)

Fairly standard SQL, but some databases might need substr() or substring() instead of right() and left().

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 HABO
Solution 3 Hart CO
Solution 4 Gordon Linoff

Related Questions