I have in my table the document field with the following data:
How do I select only the numbers of these records by removing the letters and special characters?
DOCUMENTO
CPF-12345698-35
CPF=05604968-34
CPF-:033369328-32
CPF-056904968-31
Result: From: CPF-12345698-35 To: 1234569835
The only way doing for the bank would be to create a Function
You can do the following select :
select replace(replace('12345698-35','-',''),'.','')
Do the following:
UPDATE CLIENTES SET CPF = REPLACE (CPF, '-' , '')
And then
UPDATE CLIENTES SET CPF = REPLACE (CPF, '.' , '')
Ready, just the numbers will stay.
Old question but can be useful to someone as it was for me.
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
I could do this too:
select replace('12345698-35',REGEXP '^[a-zA-Z]+$','')