remove sql zeros

Asked

Viewed 63 times

2

I have a field EUW010598260, and needed you to stay EUW1598260

I need to remove a specific field

SQL:

SELECT 'EUW' + substring(campo,patindex('%[^EUW0 ]%',campo + ' ')
       ,len(campo)) AS campo1
       ,campo2
  FROM [tabela]
 WHERE campo3 = 'cp'
   AND campo LIKE '%EUW0%'
sql

  • if the EUW0 prefix is always at the beginning of the Voce string you can use the SUBSTR.

  • what complicates is EUW010 has to stay EUW1. If it’s EUW012 it would be EUW12, right? The question is unclear. You need to explain what the pattern of this string is.

1 answer

0

You can do this through REGEX:

select REGEXP_REPLACE(campo,'EUW.1.','EUW1') from tabela

Thus the previous and subsequent '0' to 1 will be discarded.

  • I don’t quite understand your question. If you just replace EUW0 with EUW you can do a common replace

  • I want to remove the 0 EUW010=> euw1

  • only 'select REGEXP_REPLACE(field,'EUW010','EUW1') from table ' will be sufficient

  • but if there’s a two in the middle you’ve been xD

  • They’re always the same number of digits?

  • select REGEXP_REPLACE(field,'EUW0[1-9]0', 'EUW' || (select substr(field,5,1) from table Where REGEXP_LIKE (name,'EUW0[1-9]0'))) from table

Show 1 more comment

Browser other questions tagged sql

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