I have a field in the table Clientes with name CPFCNPJ but its data is formatted with points and hyphens and would like to do a SELECT in> formatting this data without punctuation.
Is it possible to perform this type of search in SQL Server?
Running a query, if your database is just numbers:
SELECT CPFCNPJ
FROM tabela
WHERE CPFCNPJ = REPLACE( REPLACE( REPLACE('111.222.333-00', '.', ''), '-', ''), '/', '' )
Retrieving characters for query
SELECT CPFCNPJ
FROM tabela
WHERE REPLACE( REPLACE( REPLACE(CPFCNPJ, '.', ''), '-', ''), '/', '' ) = '1112223300'
If there are in your database formatted and you wanted to "convert them":
UPDATE tabela
SET CPFCNPJ = REPLACE( REPLACE( REPLACE(CPFCNPJ, '.', ''), '-', ''), '/', '' )
You can do in a more "similar" way with 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
Result:
abcaisdytAYZ
Another way is to create a function that returns only letters (for use 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
How to use it:
SELECT [dbo].[fn_DevolveLetras]('abc1234-6a,9*isd(yt«?56AYZ')