How to convert a VARCHAR(30) FIELD WITH SPECIAL CHARACTERS TO NUMBER?

Asked

Viewed 1,533 times

1

I have a seemingly simple question, but I’ve been racking my brain for a few hours, so I decided to ask for help.

When executing the below query it normally executes.

Select campo1, campo2 from tabela;

I got the following result:

campo 1 = 296998.00 ("sempre um numero")
campo 2 = 2,5       (Vários resultados, compostos por caráteres especiais, "%", ",", "Letras" e etc..

I just want to do the campo1 * campo2 generating a third field resulting from the operation.

Select campo1, campo2, (campo1 * campo2) as vlr_taxa from tabela;

However this query returns the following error:

Message 8114, Level 16, Status 5, Line 2 Error Converting data type sweep to Numeric.

I researched a lot on the internet and here in the community, but I could not find a simple solution to the case.

It follows publications I’ve seen, but they haven’t solved my problem.

CAST and CONVERT (Transact-SQL)

Conversion from VARCHAR to INT

database int varchar sql-server-2005

  • You can pass a list of data you have in campo1 and campo2?

  • Thank you for intersere in helping. I am attaching an Excel with 100 lines that extracts from the database.

  • The following is a link to the file - https://drive.google.com/file/1edbyqHPe0h5DnSOIROJwSWzUAKYTtEwV/view?usp=sharing

  • From what I’ve seen of the column data, you’ll have to do some replace (for example by removing the %), to normalize the data, ie leave in the same pattern, then make the change as suggested by Ronaldo. I strongly recommend typing this column as decimal, so it doesn’t happen that and in need of conversions.

  • Again thanks for the feedback, I’m beginner and kind of need to see designed to understand. Would you have some example of how I could use replace for this function?

1 answer

2


Whereas the decimal separators in the field are in the same culture as you are working on, this command converts varchar to numeric

select cast(campo1 as float) * cast(campo2 as float) as vlr_taxa from tabela

Otherwise it will be necessary to ensure this (through replace for example)

  • Ronaldo, I did the test you asked for but unsuccessfully. "Message 8114, Level 16, Status 5, Line 1 Error Converting data type varchar to float"

  • The value of this varchar is using what as decimal delimiter? Comma or dot?

  • Ronaldo, so I evaluated the system where users perform the insertion of data is not treating the entered data, It was a bit messy. Ex: has fields with " ", "%", "text" and etc, so I evaluated most of the records are with the delimiter "," . I will have to evaluate a way to remove the special characters and then convert to number and be able to perform the calculation.

  • I get it. Replaces would be enough. But because it is a wide variety of differences, it needs to be analyzed according to what appears. Edit the question stating which possible entries for that column.

  • Ronaldo thought this publication, https://stackoverflow.com/questions/1120198/most-efficient-way-to-remove-special-characters-from-string just didn’t understand if it could fit me.

  • If you need something in SQL, check that answer

Show 1 more comment

Browser other questions tagged database int varchar sql-server-2005

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