|
Group: Anonymous / Guest
Posts: 57,
Visits: 251
|
I have looked over your posts but when run erroneously, I sent my example to you for a failure
|
|
Group: StrataFrame Developers
Posts: 3K,
Visits: 2.5K
|
Hopefully this makes sense. You'll need to substring after either the first '-' or the second '-' depending on whether there are 2 '-' characters. DECLARE @temp TABLE ( Field1 VARCHAR(30), Field2 VARCHAR(20) ); INSERT INTO @temp VALUES ('PC-04/08/16-008','04/08/2016'), ('PC-07/06/2016-006','07/06/2016'), ('PC-08/10/2017-010','08/10/2017'), ('PC-08/12/2017-012','08/12/2017'), ('PC-10/01/2015-2','10/01/2015'), ('PC-25/09/2017-009','25/09/2017'), ('PT-01/02/2015-001','01/02/2015'), ('PT-03/11/17-011','03/11/2017'), ('PT-05/04/2015-004','05/04/2015'), ('PT-15/07/2016-07','15/07/2016'), ('PT-20/05/2016-005','20/05/2016'), ('PT-31/03/2015-003','31/03/2015'), ('PT-003', '01/01/2015') SELECT MAX(T.StarNumber) Max_StringComparison, MAX(CONVERT(INT, T.StarNumber)) Max_NumberComparison FROM ( SELECT CASE WHEN CHARINDEX('-', T.Field1, 4) = 0 /* Only has 1 '-' */ THEN SUBSTRING(T.Field1, CHARINDEX('-', T.Field1, 1) + 1, 1000) /* Substring after the first '-' */ ELSE SUBSTRING(T.Field1, CHARINDEX('-', T.Field1, 4) + 1, 1000) /* Substring after the second '-' */ END StarNumber
|
|
Group: Anonymous / Guest
Posts: 57,
Visits: 251
|
Suppose in SQL Server 2005 I have the TABTHUCHI data table has the following fields: IDSOPHIEUTHUCHI // Primary Key RIGHT ... | IDSOPHIEUTHUCHI | NGAY | | PC-04/08/16-008 | 04/08/2016 | | PC-07/06/2016-006 | 07/06/2016 | | PC-08/10/2017-010 | 08/10/2017 | | PC-08/12/2017-012 | 08/12/2017 | | PC-10/01/2015-2 | 10/01/2015 | | PC-25/09/2017-009 | 25/09/2017 | | PT-01/02/2015-001 | 01/02/2015 | | PT-03/11/17-011 | 03/11/2017 | | PT-05/04/2015-004 | 05/04/2015 | | PT-15/07/2016-07 | 15/07/2016 | | PT-20/05/2016-005 | 20/05/2016 | | PT-31/03/2015-003 | 31/03/2015 | |
|
At the IDSOPHIEUTHUCHI field, you will see the letter "PT" or "PC", the year will be 2 characters or 4 characters and the last digit must be PT-(dd)/(MM)/(yyyy) - (***) Use (*) is the incremental number generated by the system, the length is determined by the number of asterisks, a maximum of 9. Example: HD (**) includes 99 numbers running from HD01, HD02 to HD99. HD (**) includes 999 numbers running from HD001, HD002 to HD999. Use (MM) to get the current month. Use (years) to retrieve the current year (last two digits of the year). Use (yyyy) to get the current year (all 4 numbers). Use (dd) to get the current date. According to the above data, you want to get the largest ID of 2015 "PT". Find the last digit of 2015: PT-05/04/2015-004, the largest ID 2015 will be 005, and assume The largest ID number of this date is 14/04/2015 will be: PT-14/04/2015-005, when taking the largest number you must also note Year can be 2 digits or 4 digits and format input. If the input format: PT-(dd)/(MM)/(yy)-(***) output: PT-14/04/15-005 If the input format: PT-(dd)/(MM)/(yyyy)-(*****) output: PT-14/04/2015-00005 If the input format: PT-(***) output: PT-005 Similar to other years for "PT" or "PC" and the largest ID date. If the "PT" format of the above data: If the input format: PT-(dd)/(MM)/(yyyy)-(***) and "14/04/2016", the output: PT-14/04/2016-008 If the input format: PT-(dd)/(MM)/(yyyy)-(***) and "14/04/2017", the output: PT-14/04/2017-012 If the input format: PT-(***) and "14/04/2017" is output PT-012 If the "PC" format of the above data: If the input format: PC-(dd)/(MM)/(yyyy)-(***) and "14/04/2015" is output: PC-14/04/2015-003 If the input format: PC-(dd)/(MM)/(yyyy)-(**) and "14/04/2016" then output: PC-14/04/2016-09 If the input format: PC-(dd)/(MM)/(yy)-(***) and "14/04/2017" then output: PC-14/04/17-013 How can SQL Server retrieve the largest ID in string form as shown above ?
|