Remove "." /" and "-" from varchar in select

Asked

Viewed 7,723 times

0

I’m performing a query for a client where it requested that the CNPJ/CPF of companies/customers be displayed without the dots, bars and dashes. For example the CNPJ 08.595.551/0001-57 should be shown 08595551000157.

I tried using the command REPLACE as follows: REPLACE(E.[CNPJ/CPF],'.', '')

However it only works for one of the characters, there is some way to remove all so that only the numbers are displayed?

sql-server sql-server-2008 characters
  • 2

    The same ideal would be to record without it in the database, and let the interface take care to present the data with dots and etc.

  • I agree with @Lucas, the best way is to clean the database and then use a function (sql or code) to apply the mask

  • Actually it would be much easier, however it uses an ERP system that when registering customers already adds with points and everything else.

  • As of 2017 version of SQL Server is available the TRANSLATE function, which allows to execute the action with a single call to the function.

1 answer

4


You must use several Places:

REPLACE(REPLACE(REPLACE(E.[CNPJ/CPF],'.', ''),'-', ''),'/', '')

  • It worked, thank you very much Marcelo!

Browser other questions tagged sql-server sql-server-2008 characters

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