Query data with mask - SQL Server

Asked

Viewed 203 times

2

I have the following structure:

http://sqlfiddle.com/#! 9/29027c4/1

CREATE TABLE PATRIMONIO (
    NUM_PATRIMONIO VARCHAR(100),
    DESCRICAO_PATRIMONIO VARCHAR(100)
);

INSERT INTO PATRIMONIO 
VALUES ('HDT-2725','VW/GOL 1.0 GER IV'),
('HDT-2744','VW/GOL 1.0 GER IV'),
('HDT-2751','VW/GOL 1.0 GER IV'),
('HDT-2764','Fiat Palio Celebration 1.4 Fire Flex 8V 4p'),
('002376','Notebook Dell i5 8GB 1TB')

With the following select:

SELECT NUM_PATRIMONIO, DESCRICAO_PATRIMONIO FROM PATRIMONIO

This is not the complete structure of the table, this was just an example that came from how the data is appearing. At the moment, they are coming like this:

NUM_PATRIMONIO  DESCRICAO_PATRIMONIO
HDT-2725        VW/GOL 1.0 GER IV
HDT-2744        VW/GOL 1.0 GER IV
HDT-2751        VW/GOL 1.0 GER IV
HDT-2764        Fiat Palio Celebration 1.4 Fire Flex 8V 4p
002376          Notebook Dell i5 8GB 1TB

And I need the return to be just the vehicles that are wearing the mask of the plates XXX-0000. The bank only has Brazilian plates, so other formats are irrelevant.

I’ve tried to find some function or even ways to do this query on google, but what I always find are people looking for how to consult a Pf without mask and put the mask at the time of the query. But I don’t want to put the mask, I need to filter the data that already have this mask XXX-0000.

The database is SQL Server.

sql

2 answers

4


Sqlserver does not support regular expressions by default, but an approximation with like is possible. For example:

SELECT * FROM PATRIMONIO WHERE NUM_PATRIMONIO LIKE '___-____'

It will bring the data you want, but it does not guarantee that there are only letters in the first block nor that there are only numbers in the second. To secure the mask, you can use the syntax of "range":

SELECT * FROM PATRIMONIO WHERE NUM_PATRIMONIO LIKE '[A-Z][A-Z][A-Z]-[0-9][0-9][0-9][0-9]'

This returns what you need. sqlserver documentation contains some very interesting examples of what can be done using like.

https://docs.microsoft.com/pt-br/sql/t-sql/language-elements/like-transact-sql?view=sql-server-2017

  • Thank you very much. That’s exactly what I needed, and I had no idea how easy it was!

1

Good Afternoon,

I recommend to treat the masks in some application.

But if you want directly in SQL try the following query:

SELECT DESCRICAO_PATRIMONIO 
  FROM PATRIMONIO
 WHERE UPPER(NUM_PATRIMONIO) LIKE UPPER('HDT-2725')

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.