Replace last two characters in column

Question

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.

What database? Available functions may vary. Add a tag or edit your question. — Jan Doggen, May 22 '13 at 16:23
IMO, the question 'Replace last two characters in column' is wrong, since the characters in this case are always the third and the fourth. — Fabian Bigler, May 22 '13 at 16:33
@FabianBigler: Not 3rd and 4th--5th and 6th. "201201" means the first accounting period in 2012. The 13th accounting period in 2012 would be "201213". One of my earliest professional programming jobs was writing software to determine shareholder value for a multi-national Fortune 100 company. The number of accounting periods varied from 7 to 54 per year. (Before you ask, no, I can't explain that.) — Mike Sherrill 'Cat Recall', May 22 '13 at 16:40
@Mike: Ok, thanks! I got confused because he was talking about Period '13' so I thought he meant the business years. After rereading it was obvious. :) — Fabian Bigler, May 22 '13 at 16:44

Fabian Bigler · Accepted Answer · 2013-05-22T16:59:19.950

3

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?

edited May 22 '13 at 16:59

answered May 22 '13 at 16:24

Fabian Bigler

10,403
6
47
70

Many thanks to all, all good and valuable answers but I can only mark one as "the" answer. – Tim Richards May 22 '13 at 17:22

score 2 · Answer 2 · answered May 22 '13 at 16:42

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

score 1 · Answer 3 · answered May 22 '13 at 16:41

1

Likewise, RIGHT() could be used.

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

answered May 22 '13 at 16:41

Hart CO

34,064
6
48
63

score 1 · Answer 4 · answered May 22 '13 at 16:43

1

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().

answered May 22 '13 at 16:43

Gordon Linoff

1,242,037
58
646
786

score -3 · Answer 5 · edited Apr 24 '22 at 10:31

-3

SELECT LEFT(PERIOD, Len(PERIOD) - 2)
FROM   @b_s_summary

edited Apr 24 '22 at 10:31

RF1991

2,037
4
8
17

answered Apr 10 '20 at 14:40

wissam

1

1

Looks like that will just fetch the year. – Scratte Apr 10 '20 at 16:50
1

Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, **can you [edit] your answer to include an explanation of what you're doing** and why you believe it is the best approach? – Jeremy Caney Apr 26 '22 at 02:29

Replace last two characters in column

5 Answers5

Linked