Question is like this, there is a date field is 8 digits in length (20200506) is similar to this, but we migrated data, there is a (2017829) (2017109) this kind of appearance, want to ask is there any way to use SQL logic to check the value if the length is not as the eight, the string of Numbers are split into four year ago, and day and month, and then to zero padding, become (20170829) (20171009) like this,

CodePudding user response:

2017109, 20170109 and 20171009, there are two kinds of this value method, how to determine the final is 20171009?
Will there be 201711 this kind of situation?

CodePudding user response:

reference 1st floor csdn_castiel response:

2017109, 20170109 and 20171009, there are two kinds of this value method, how to determine the final is 20171009?
Will there be 201711 this kind of situation?

this is 10170101

CodePudding user response:

refer to the second floor yu zhe reply:

Quote: refer to 1st floor csdn_castiel response:

2017109, 20170109 and 20171009, there are two kinds of this value method, how to determine the final is 20171009?
Will there be 201711 this kind of situation?

10170101

this is

201711 the examples I give wrong

2017109=& gt; 20171 _09 and 201710 _9 this how to solve?
The last is 20170109 or 20171009

CodePudding user response:

reference csdn_castiel reply: 3/f

Quote: refer to the second floor yu zhe reply:

Quote: refer to 1st floor csdn_castiel response:

2017109, 20170109 and 20171009, there are two kinds of this value method, how to determine the final is 20171009?
Will there be 201711 this kind of situation?

10170101

this is

201711 the examples I give wrong

2017109=& gt; 20171 _09 and 201710 _9 this how to solve?
The last is 20170109 or 20171009

I will put all possible underlined or points or slashes out all before... To get zero padding

CodePudding user response:

reference 4 floor yu zhe reply:

I'll put all possible underlined or points or slashes out all before... To get zero padding

You may still didn't understand what I mean

2017109 there are two split way this value
2017 09 1 and 10 September 2017
The results of the two split are 20170109 and 20171009
Encounter this kind of circumstance how to deal with the

_9 _09 (20171 and 201710, I write this just let you see it is two kinds of split method)

CodePudding user response:

Splicing function processing

2017109=& gt; 20171 _09 and 201710 _9 this how to solve?
The last is 20170109 or 20171009;

SELECT CONCAT (SUBSTR (' 20191 _07 ', 1, 4), '0', SUBSTR (' 20191 _07 ', 5, 1), SUBSTR (' 20191 _07, 7, 2)); Results: 20170109;


SELECT CONCAT (SUBSTR (' 201710 _9, 1, 6), '0', SUBSTR (' 201710 _9, 8, 1)); Results: 20171009;

If you only have these two types, you can put the inside of the constants to replace the fields you want, use the update process;
Assumptions, table named, field called date1, SQL is as follows:
- 20171 _09
The update table1 set date1=CONCAT (SUBSTR (date1, 1, 4), '0', SUBSTR (' 20191 _07 ', 5, 1), SUBSTR (date1, 7, 2)) where INSTR (date1, '_')=6;

- 201710 _9
The update table1 set date1=CONCAT (SUBSTR (date1, 1, 6), '0', SUBSTR (date1, 8, 1)) where INSTR (date1, '_')=7;

CodePudding user response:

Can directly use statement case.. when.. Determine the length of the string, is a 201712, 20171011 is the second, 2017101 and 2017110 is the third, again to the third it is ok to make a judgment, the rest is just insert 0