Data search without formatting sqlserver with select

Asked

Viewed 60 times

0

I have a field on the table Clientes by name CPFCNPJ but your data is formatted with dots and hyphens and would like to make a SELECT formatting this data without punctuation.

It is possible to perform this type of search in SQL Server?

sql sql-server

  • What you want is that in the result of the query the formatting is removed or the goal is to search without formatting? The statement is dubious... Could post example?

2 answers

1


Consulting

Executing a query, in case your bank is only numbers:

SELECT CPFCNPJ
FROM tabela
WHERE CPFCNPJ = REPLACE( REPLACE( REPLACE('111.222.333-00', '.', ''), '-', ''), '/', '' )

Removing the characters for consultation

SELECT CPFCNPJ
FROM tabela
WHERE REPLACE( REPLACE( REPLACE(CPFCNPJ, '.', ''), '-', ''), '/', '' ) = '1112223300'

Applying

If they exist in your formatted database and wanted to "convert them":

UPDATE tabela
SET CPFCNPJ = REPLACE( REPLACE( REPLACE(CPFCNPJ, '.', ''), '-', ''), '/', '' )

Useful links:

REPLACE

  • in my case I wanted to take the score so that the user did not have to type points and hyphens to locate such a customer. https://i.imgur.com/2paRRhW.png is thus the data in my bd

  • @Alltairmentor added the 2nd select in "consulting" in my reply.

  • Thanks for the Rbz instructions but when I perform the query to remove the characters it comes back null without any result. Is it necessary to declare the cpfoucnpj? thank you

0

You can do it in a more "like" way RegEx:

DECLARE @Input VARCHAR(100) = 'abc1234-6a,9*isd(yt«?56AYZ'

WHILE PATINDEX('%[^a-z]%', @Input) > 0
    SET @Input = STUFF(@Input, PATINDEX('%[^a-z]%', @Input), 1, '')

PRINT @Input

Upshot:

abcaisdytAYZ

Another way is to create a function that returns only letters (to be used in queries):

CREATE FUNCTION [dbo].[fn_DevolveLetras](@input VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN 
    WHILE PATINDEX('%[^a-z]%', @input) > 0
    SET @input = STUFF(@input, PATINDEX('%[^a-z]%', @input), 1, '')
    RETURN @input
END

Form of use:

SELECT [dbo].[fn_DevolveLetras]('abc1234-6a,9*isd(yt«?56AYZ')

Browser other questions tagged sql sql-server

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