Regex pattern is throwing error when i'm using it in where Clause

• krishnamurali2489

  Mr or Mrs. 500

  Points: 517

  More actions

  April 18, 2016 at 1:47 pm

  #315712

  Hi All,

  I wanted to use Regex Pattern in my where clause to narrow down the records , but the query is throwing error when running.

  I have a table named Detail

  CREATE TABLE [dbo].[Audit](

  [AuditId] [numeric](18, 0) IDENTITY(1,1) NOT NULL,

  [SystemId] [int] NULL,

  [Module] [varchar](255) NULL,

  [LoginId] [varchar](20) NOT NULL,

  [AccountNumber] [char](10) NULL,

  [Detail] [text] NULL,

  [ActionDate] [datetime2](3) NOT NULL

  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  GO

  SET ANSI_PADDING OFF

  GO

  ALTER TABLE [dbo].[Audit] ADD CONSTRAINT [DF_Audit_ActionDate] DEFAULT (getdate()) FOR [ActionDate]

  GO

  when i try to run the below query

  SELECT * FROM Audit WHERE Detail REGEXP '^b';

  Im getting below error :

  An expression of non-boolean type specified in a context where a condition is expected, near 'REGEXP'.

  Please throw some light on it.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  April 18, 2016 at 2:23 pm

  #1872900

  you posted in SQL2008 forum, so my questions are for that platform. i think you might be posting a MySQL question in a SQL Server centric forum though.

  Since SQL Server does not have built in Regular Expressions, did you install a CLR (Common Language Runtime) to install some CLR Reg ex tools?

  or are you assuming that SQL2008 has it built in?

  a WHERE statement MUST have an equals sign to be evaluated.

  assuming you had a scalar function named REGEXP, i'd expect ti to maybe look something like this:

  SELECT * FROM Audit WHERE dbo.REGEXP(Detail,'^b') = 1;

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!