How do I write a SQL Query to fetch data according to a specific format/ pattern of String?

Question

I need to write a SQL query to fetch data according to a specific format of String. I need to fetch those records where the LOC column of my query looks like the following:

Cx-xxx-Lx
Or
Cxx-xxx-Lx
Or
x-xxx-Lx
Or
xx-xxx-Lx
Or
xxxxxLx_x
Or
xxxxxLx_xx
Or
BxxxLLxxxx
Where :-
x is a number (0 to 9)
L is a letter (A to Z)

I have filtered the LOC column to fetch data where the length of the record is either 9 or 10. Although this is fetching correct data from the DB, this is not a correct way of doing so.

My current SQL:

select * from table
where length(LOC) in (9,10)

Any help would be appreciated.

Use REGEXP_LIKE.

Thorsten Kettner
– Thorsten Kettner

2017-06-07 11:15:43 +00:00
Commented Jun 7, 2017 at 11:15 — Thorsten Kettner
– Thorsten Kettner, Commented Jun 7, 2017 at 11:15

Gordon Linoff · Accepted Answer · 2017-06-08 00:53:19Z

3

You can use regular expressions. Something like:

where regexp_like(LOC, '^C?[0-9]{1,2}-[0-9]{3}-[A-Z][0-9]$') or
      regexp_like(LOC, '^[0-9]{5}[A-Z][0-9]_[0-9]{1,2}$') or
      regexp_like(LOC, '^B[0-9]{3}[A-Z]{2}[0-9]{4}$')

You can combine these into one regular expression using |. I think it is easier to follow and debug as three separate expressions.

edited Jun 8, 2017 at 0:53

user5683823

answered Jun 7, 2017 at 11:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

How do I write a SQL Query to fetch data according to a specific format/ pattern of String?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related