CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
user3010406
user3010406

Reputation: 579

Get MAX value if column has a certain format

SQL Server 2008 R2

I have a table similar to this:

Example table:

ID Column
---------------
xxx1234
xxx12345
xxx123456
20150001

I am trying to get a conditional MAX value depending on the value of the column based on whether it meets as certain format. Using the above example, the fourth record, 20150001, represents a "good record" because it contains the current year, and the start of an increment. So, in my table, records that are considered "good" (those subject to the query I am trying to write) have the format "year + increment". The first three, that do not follow this format, should not be conditioned to the query since they don't match this format and should not be subject when computing the max value. Those are bad records. In the above example, the expected result would be "20150002".

The MAX query is simple enough to write, however I am wondering about an approach where I can sanitize the query to only include those records whom meet the particular format, and increment the last four digits (0001 to 0002).

TIA!

Upvotes: 0

Views: 311

Answers (2)

Ron Smith
Ron Smith

Reputation: 3266

You can use the isdate function to filter out ID Columns that do not start with a valid year, and isnumeric to make sure the last 4 characters of the ID Column are valid increments. You also want the len to be 8, given this criteria. You can accomplish all this in the where clause:

-- load test data
declare @Example_Table table(ID_Column varchar(10))
insert into @Example_Table values
    ('xxx1234'),
    ('xxx12345'),
    ('xxx123456'),
    ('20150001')

-- return max valid ID_Column
select max(ID_Column) as max_ID_Column
from @Example_Table
where isdate(left(ID_Column,4)) = 1
    and isnumeric(right(ID_Column,4)) = 1
    and len(ID_Column) = 8

-- increment max valid ID_Column
update @Example_Table
set ID_Column = cast(ID_Column as int) + 1
where isdate(left(ID_Column,4)) = 1
    and isnumeric(right(ID_Column,4)) = 1
    and len(ID_Column) = 8

select * from @Example_Table

ID_Column
----------
xxx1234
xxx12345
xxx123456
20150002

Upvotes: 1

jtimperley
jtimperley

Reputation: 2544

You could use a regular expression to verify a correct year. The second half of the regular expression I taylored to your examples of 0001 and 0002, this could be opened up by adding '[0-9]' for each digit you're expecting.

DECLARE @Sample VARCHAR(30) = '20150001';

SELECT CASE WHEN (@Sample LIKE '[12][09][0-9][0-9]000[12]') THEN 'Yes' ELSE 'No' END;

SELECT
    SUBSTRING(@Sample, 1, 4),
    SUBSTRING(@Sample, 5, 4),
    CASE WHEN (SUBSTRING(@Sample, 1, 4) LIKE '[12][09][0-9]') THEN 'Yes' ELSE 'No' END,
    CASE WHEN (SUBSTRING(@Sample, 5, 4) LIKE '[0-9][0-9][0-9][0-9]') THEN 'Yes' ELSE 'No' END;

Upvotes: 1

Related Questions