SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Substring getting last 4 year value condition
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cplusplus
Aged Yak Warrior

536 Posts

Posted - 11/05/2013 :  11:46:14  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 11/05/2013 :  11:51:07  Show Profile  Reply with Quote
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]'
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
329 Posts

Posted - 11/05/2013 :  12:03:51  Show Profile  Reply with Quote
You also might try
WHERE ISDATE(Data_Set) = 1


djj
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
372 Posts

Posted - 11/05/2013 :  16:53:33  Show Profile  Reply with Quote
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]'
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000