Regular Expression with the Sql Query

Question

SELECT a.[PREMISES],a.[Class] FROM [Legacy].[dbo].[MyTables] as a;

Above query returns :

Can you tell me how to remove 210, and 211, from the query column a.[Class] ?

Note : I just gave an example.I need to have a generic solution which will apply for all the rows of that a.[Class] column.

Note 2 : Format of the Class column is like this.numeric then , after that string.I need to remove first 2 parts.Need to keep only the string part.

SELECT a.[PREMISES], SUBSTRING(a.[Class], 5, 100) FROM [Legacy].[dbo].[MyTables] as a; — Chris Pickford
– Chris Pickford, Commented Jun 20, 2016 at 9:39
@JaydipJ Yes,It's like this numeric then , after that string — Sampath
– Sampath, Commented Jun 20, 2016 at 9:44

gofr1 · Accepted Answer · 2016-06-20 10:46:05Z

2

You don't necessarily need to use a regular expression here. You could try using REPLACE:

SELECT  a.[PREMISES],
        REPLACE(REPLACE(a.[Class],'210, ',''),'211, ','') as [Class] 
FROM [Legacy].[dbo].[MyTables] as a;

To remove all before first ,:

SELECT  a.[PREMISES],
        LTRIM(RIGHT(NULLIF(a.[Class],''),LEN(a.[Class])-CHARINDEX(',',a.[Class]))) as [Class]
FROM [Legacy].[dbo].[MyTables] as a;

This query, for that data

PREMISES            Class
Victoria Ln         210, Single Family (no style specified)
West 10th Street    211, Ranch, Single Family
East 2nd Street     Single Family

Will give you this output:

PREMISES            Class
Victoria Ln         Single Family (no style specified)
West 10th Street    Ranch, Single Family
East 2nd Street     Single Family

edited Jun 20, 2016 at 10:46

answered Jun 20, 2016 at 9:38

gofr1

16k11 gold badges47 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Sampath Over a year ago

This won't work.I just gave an example.I need to have generic solution which will apply for all the rows.Thanks.

Chris Pickford Over a year ago

Then you need to improve the quality of your question.

Sampath Over a year ago

@ChrisPickford Done that.

gofr1 Over a year ago

You need to remove first 5 symbols from [Class] row? Or there are some cases?

Sampath Over a year ago

It's like this numeric then , after that string.I need to remove first 2 parts.Need to keep only the string part.

|

Sujeet Sinha · Accepted Answer · 2016-06-20 09:42:57Z

2

You can use the following query to return the result starting from ,.

SELECT  a.[PREMISES],
        SUBSTRING(a.[Class], CHARINDEX(',',a.[Class]) + 1, LEN(a.[Class])) as [Class] 
FROM [Legacy].[dbo].[MyTables] as a;

answered Jun 20, 2016 at 9:42

Sujeet Sinha

2,4332 gold badges20 silver badges27 bronze badges

Comments

mvisser · Accepted Answer · 2016-06-20 09:50:12Z

0

Just remove all numeric characters from a string.

SELECT a.[PREMISES],
    SUBSTRING(a[Class], PATINDEX('%[a-z]%', a.[Class]), LEN(a.[Class])) 
  FROM [Legacy].[dbo].[MyTables] a

answered Jun 20, 2016 at 9:50

mvisser

6705 silver badges11 bronze badges

Comments

Vahid Farahmandian · Accepted Answer · 2016-06-20 10:04:15Z

0

Try this:

CREATE FUNCTION Fun_Refiner ( @val VARCHAR(MAX) )
RETURNS VARCHAR(MAX)
AS
BEGIN
    WHILE 1 = 1
        BEGIN
            IF PATINDEX('[0-9]%', @val) > 0 OR SUBSTRING(@val, 1, 1) = ','
                SET @val = RTRIM(LTRIM(REPLACE(@val,SUBSTRING(@val,PATINDEX('[0-9,]%',@val), 1), '')));
            ELSE
                BREAK;
        END;
      RETURN @val
END;

And just call it like this:

SELECT a.[Class], dbo.Fun_Refiner(a.[Class]) FROM YourTable AS a

answered Jun 20, 2016 at 10:04

Vahid Farahmandian

6,6227 gold badges48 silver badges68 bronze badges

Collectives™ on Stack Overflow

Regular Expression with the Sql Query

4 Answers 4

12 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

12 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related