0
I am making a query to get all the projects that are being executed:
SELECT p.id FROM projetos p WHERE p.status LIKE 'Execução'
An example of the result:
COD00012347
COD00012348
COD01212349
COD00123410
COD00123411
I would like to apply a regex so that my result is only the numbers (disregarding the 0 to the left):
12347
12348
1212349
123410
123411
I tried to apply the function SUBSTRING() but I haven’t had good results. I’m learning SQL yet, what I can do??
2
If your id is this fixed amount, I don’t see the need for regex, just give an integer cast that the zeroes on the left will disappear after the substring, example:
select cast (SUBSTRING('COD00012347',4,8) as int ) -- resulta em 12347
Updating
if the letters at the beginning also have a dynamic quantity, then it is easier with regex even using patindex, example:
select substring('COD00012347',PATINDEX('%[1-9]%','COD00012347'),len('COD00012347')) -- regex pega o index do primeiro numero de 1 a 9, depois a quantidade de caracteres da string conforme sua dica
It helps me! For in the place of 8 can I put len(atributo).
but what can I put in place of the four so that it starts counting from the last letter??
then in that case I think I’d better use regex anyway, I’ll update the answer
Worked perfectly!
Browser other questions tagged sql sql-server string substring
You are not signed in. Login or sign up in order to post.
But what was the problem you encountered with the substring function? Ever thought about using the RIGHT function?
– anonimo
@anonymity to qnt of zeros may vary for example.
– Gabriel Hardoim
If you only want the last 6 characters, maybe
RIGHT(coluna, 6)work.– Sam
@anonimo still this 6 may vary. I edited the question to show a more specific result.
– Gabriel Hardoim
If you need that separate number, it wouldn’t be better to store it in a different column?
– Woss
@anonymity does not have written permission.. how can I do this?
– Gabriel Hardoim