8
I have in my table the field documento with the following data:
How do I select only the numbers of these records by removing the letters and special characters?
| DOCUMENT |
|---|
| CPF-12345698-35 |
| CPF=05604968-34 |
| CPF-:033369328-32 |
| CPF-056904968-31 |
Expected result: From: CPF-12345698-35 To: 1234569835
5
The only way to do it for the bank would be to create a Function
Note: maybe it is not so performative, but it would be a way, maybe not the best in relation to big data but, if it is little data, or a screen with pagination, will not have so much performace problems.
From what I realized would be a practical way for your conversion.
Function:
Note: Where is testdb put the name of your bank and run this creation script in the database
DELIMITER $$
CREATE FUNCTION `testdb`.`GetNumber` (field varchar(100))
RETURNS VARCHAR(100)
BEGIN
DECLARE ls INTEGER;
DECLARE i INTEGER;
DECLARE str varchar(100);
SET ls = (select length(field));
SET i = 1;
SET str = "";
WHILE i <= ls DO
IF ((substring(field, i,1) REGEXP '[0-9]') <> 0) THEN
SET str = CONCAT(str, convert(substring(field, i,1) USING UTF8));
END IF;
SET i = i + 1;
END WHILE;
RETURN str;
END;
Using:
select GetNumber('0cpf:dlbaa25545..5531A');
Exit:
3
You can do the following select:
select replace(replace('12345698-35','-',''),'.','')
thanks for the attention, in the example above is removing the hyphen and the point, as would take out the letters 'CPF' or any other type of letter?
for that you have to do something like this: SELECT person, replace(replace(replace(replace(phone_number,' ','),'(',''),')',''-'),'-',''') as phone_number FROM people WHERE phone_number RLIKE ' [+]? [-() 0-9]+$'; using Where to pick up only what you need
using both replace will make your query slow, think about it without forgetting the volume of data, number of transactions executed in a given time interval, amount of total transactions in the referred bank and how much this can affect the system.
2
Do the following:
UPDATE CLIENTES SET CPF = REPLACE (CPF, '-' , '')
And then
UPDATE CLIENTES SET CPF = REPLACE (CPF, '.' , '')
All right, it’ll just be the numbers.
I did not vote against, but I do not think that is the purpose of the question. The idea was, as the title of the question states, to remove the special characters in the SELECT, and not simply extirpate them from the database. Hence remove in the database has been suggested before and the author of the question said it is not what he wants.
0
Query:
DECLARE @temp TABLE
(
string NVARCHAR(50)
)
INSERT INTO @temp (string)
VALUES
('003Preliminary Examination Plan'),
('Coordination005'),
('Balance1000sheet')
SELECT SUBSTRING(string, PATINDEX('%[0-9]%', string), PATINDEX('%[0-9][^0-
9]%', string + 't') - PATINDEX('%[0-9]%',
string) + 1) AS Number
FROM @temp
0
I use the tip given by @user6026, but I had problem with the slowness, it seems that the function is a little slow in execution, even with only due fields treated, I did several tests.
As the idea is to treat only CPF and CNPJ, I ended up doing another function to treat only these cases, see below:
CREATE DEFINER=`root`@`localhost` FUNCTION `fLimpaCPFCNPJ`(`str` VARCHAR(20))
RETURNS varchar(20) CHARSET latin1
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
return replace(replace(replace(str, '/', ''), '-', ''), '.', '');
END
It is worth remembering that I am not DBA and I wish that those who have more experience could make the appropriate corrections.
The tests were much faster
-1
I could do that too:
select replace('12345698-35',REGEXP '^[a-zA-Z]+$','')
The mysql REGEXP function is not used for this purpose. It returns a boolean just saying whether REGEXP was found or not...
Ummmm Sorry tried to
-1
use the function case with REGEXP:
case
when numero REGEXP '^[0-9]+$' then numero
else 0
end as numero
Browser other questions tagged mysql select-sql
You are not signed in. Login or sign up in order to post.
I’ve seen an answer to that here but I can’t find it.
– Jorge B.
@bfavaretto the HTML was just to format the content!
– Vitor Molina
OK Vitor. I edited to clean your formatting marks, see how you can get the same visual result without any HTML.
– bfavaretto
Do you really need this procedure to run in the database? Why doesn’t it replace in the programming? In addition to being easier, you’ll get more performance...
– rizidoro
@rizidoro am making the conversion from one database to another using the query Insert into(values,...) Select values ... and would like to remove the letters and special characters in the select itself! but if I don’t get a solution I’ll have to accomplish in programming.
– Vitor Molina
In my view, the best way would be to actually treat this data in a high-level language.
– Jorge B.