Substring getting last 4 year value condition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Substring getting last 4 year value condition

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-11-05 : 11:46:14

I am using the following substring logic within my query. expecting just 4 digit year values, but there are lot of rows within the table has charecter based values like BKUP seems like bakup. now i want to exclude anything that has other than year value after sub string. Is it possible in the where condition.

substring(DATA_SET,7,10) YEAR,

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-05 : 11:51:07

You can use a LIKE predicate:

DECLARE @Foo TABLE (DATA_SET VARCHAR(20))

INSERT @Foo VALUES
('01-01-1900'),
('BKUP'),
('foo'),
('1234')


SELECT substring(DATA_SET,7,10) YEAR
FROM @Foo
WHERE substring(DATA_SET,7,10) LIKE '[0-9][0-9][0-9][0-9]'

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-11-05 : 12:03:51

You also might try

WHERE ISDATE(Data_Set) = 1

djj

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-11-05 : 16:53:33

Unless you are using dates far in the past or future, you can restrict the date check somewhat more:

...
WHERE substring(DATA_SET, 7, 10) LIKE '[12][0-9][0-9][0-9]'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources