Post reply

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

  • 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.

  • 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!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply