Solved

regular expression in sql server

Posted on 2015-02-23
5
74 Views
Last Modified: 2015-03-05
Ok, how can I write a regular expression which can tell me a particular row data is not correct date, out of a column , in a table with millions of records

column 1
01-Jan-15
01-Jan-15
01-Jan-15
01-Jan-15
01/Jan/15

what regular expression will return me row no. 5 from above table.
0
Comment
Question by:BeyondBGCM
  • 2
  • 2
5 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40627492
what version of SQL Server is it?

At SQL 2012 TRY_CAST() is available.

select
*
from table1
where try_cast(column1 as date) IS NULL

but the problem with this is performance my be poor on a large table (but the same is likely to be true with alternatives).
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40627543
I confirm that sql server as such does not have have regular expressions.
eventually you don't need such thing, maybe this would be enough already:

where not like '[0-9][0-9]-[JFMASOND][AEPUCO][NBRYLGPVTC]-[0-9][0-9]'
0
 

Author Comment

by:BeyondBGCM
ID: 40628409
but, we can use regular expressions in .net since 1.1 release
0
 

Author Comment

by:BeyondBGCM
ID: 40628463
ie from 2003
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40629996
yes, in .Net, but not in sql server directly
using .net means you would have to bring all the relevant table data from the server to the .net app
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question