Working with messy numeric data is one of those unavoidable realities in database development. Whether you’re importing data from CSV files, web APIs, or legacy systems, you’ll often encounter numeric values stored as strings with all sorts of unwanted characters mixed in. SQL Server’s conversion functions are pretty strict about what they’ll accept, so you need to clean up these strings before attempting any conversions.<\/p>\n\n\n\n\n\n\n\n
When SQL Server tries to convert a string to a numeric type using functions like Usually the best way around this is to systematically strip out the unwanted characters and normalize the format before attempting the conversion. This approach is typically more reliable than trying to handle every edge case during the conversion itself.<\/p>\n\n\n\n Most numeric string cleaning involves removing or replacing specific character patterns. Here are some common options:<\/p>\n\n\n\n The trick is to apply these operations in the right order. You typically want to handle the most disruptive characters first, then work your way down to the subtle formatting issues.<\/p>\n\n\n\n In this example we’ll use Suppose we have the following table and data:<\/p>\n\n\n\n Output:<\/p>\n\n\n\n The Now if we try to convert the above column directly to a numeric type, we’ll get errors on most rows:<\/p>\n\n\n\n Output:<\/p>\n\n\n\n It threw an error at line 1, due to it having a value that couldn’t be converted to our numeric type (in this case We could avoid the error by replacing Output:<\/p>\n\n\n\n This function returns In order to get the conversions to succeed, we’ll need to clean the raw data before attempting to convert it. The idea is simple in that we will strip out everything that isn\u2019t part of a valid number. In particular, we’ll remove commas, spaces, and dollar signs. We’ll also handle accounting-style negatives (e.g., One way to do this in SQL Server is by using a custom Here\u2019s an approach that handles most of the cases above:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n We can see that most of the rows were converted successfully. Two failed, because our data cleansing process didn’t catch them. This is mainly because one of the raw values was Here’s an example that uses Result:<\/p>\n\n\n\n In this case the code is a lot more concise, however, you’ll need to be careful that the arguments work for your scenario, otherwise you could end up with a mess.<\/p>\n\n\n\n With a reliable cleansing pattern in place, you can safely convert messy text-based numbers into proper numeric data types and move on with analysis or reporting.<\/p>\n","protected":false},"excerpt":{"rendered":" Working with messy numeric data is one of those unavoidable realities in database development. Whether you’re importing data from CSV files, web APIs, or legacy systems, you’ll often encounter numeric values stored as strings with all sorts of unwanted characters mixed in. SQL Server’s conversion functions are pretty strict about what they’ll accept, so you … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[10,77,90,69,61],"class_list":["post-45346","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-how-to","tag-mssql","tag-number-format","tag-string-characters","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/45346","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=45346"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/45346\/revisions"}],"predecessor-version":[{"id":45364,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/45346\/revisions\/45364"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=45346"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=45346"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=45346"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}CAST()<\/a><\/code>, CONVERT()<\/a><\/code>, or their TRY_<\/code> equivalents TRY_CAST()<\/a><\/code> and TRY_CONVERT()<\/a><\/code>, it expects the string to be in a clean, recognizable format. Throw in some extra spaces, currency symbols, formatting characters, or non-numeric junk, and you’ll either get conversion errors or NULL values where you expected numbers.<\/p>\n\n\n\nCommon Cleaning Options<\/h2>\n\n\n\n
\n
REPLACE()<\/a><\/code> for removing specific characters like currency symbols, commas, or parentheses<\/li>\n\n\n\nTRANSLATE()<\/a><\/code> to do a similar thing to REPLACE()<\/code> (although these functions work differently<\/a>)<\/li>\n\n\n\nLTRIM()<\/a><\/code> and RTRIM()<\/a><\/code> for stripping whitespace from the beginning and end<\/li>\n\n\n\nSUBSTRING()<\/a><\/code> for extracting portions of strings when the numeric part is embedded in text<\/li>\n\n\n\nPATINDEX()<\/a><\/code> for finding positions of patterns, useful for locating where numeric data starts or ends<\/li>\n\n\n\nExample<\/h2>\n\n\n\n
REPLACE()<\/code> to clean the numeric strings before conversion. We’ll also look at combining REPLACE()<\/code> with TRANSLATE()<\/code> to achieve the same result.<\/p>\n\n\n\nSample Data<\/h3>\n\n\n\n
CREATE TABLE SalesRaw (\n Id INT IDENTITY(1,1),\n RawAmount VARCHAR(50)\n);\n\nINSERT INTO SalesRaw (RawAmount)\nVALUES\n (' 1,200 '), -- extra spaces and a comma\n ('$450.75'), -- currency symbol and decimal\n ('3000'), -- clean number\n ('12abc34'), -- bad characters inside\n ('(200)'), -- negative stored in accounting style\n (NULL), -- null value\n ('7 500'); -- embedded space\n\nSELECT * FROM SalesRaw;<\/code><\/pre>\n\n\n\nId RawAmount
-- ---------
1 1,200
2 $450.75
3 3000
4 12abc34
5 (200)
6 null
7 7 500 <\/pre>\n\n\n\nRawAmount<\/code> column is VARCHAR<\/code>, which is a string type (not a numeric type). <\/p>\n\n\n\nAttempting to Convert the Data Without Cleaning<\/h3>\n\n\n\n
SELECT \n Id,\n RawAmount,\n CAST(RawAmount AS DECIMAL(18,2)) AS CleanAmount\nFROM SalesRaw;<\/code><\/pre>\n\n\n\nMsg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.<\/pre>\n\n\n\nDECIMAL(18,2)<\/code>).<\/p>\n\n\n\nCAST()<\/code> with TRY_CAST()<\/code>, but that still won’t result in a conversion:<\/p>\n\n\n\nSELECT \n Id,\n RawAmount,\n TRY_CAST(RawAmount AS DECIMAL(18,2)) AS CleanAmount\nFROM SalesRaw;<\/code><\/pre>\n\n\n\nId RawAmount CleanAmount
-- --------- -----------
1 1,200 null
2 $450.75 null
3 3000 3000
4 12abc34 null
5 (200) null
6 null null
7 7 500 null <\/pre>\n\n\n\nnull<\/code> whenever a conversion fails, and so most of our rows return null<\/code>, which means most of the conversions failed.<\/p>\n\n\n\nUsing
REPLACE()<\/code> to Clean the Data<\/h3>\n\n\n\n(200)<\/code> becomes -200<\/code>).<\/p>\n\n\n\nREPLACE()<\/code> chain.<\/p>\n\n\n\nSELECT \n Id,\n RawAmount,\n TRY_CAST(\n REPLACE(\n REPLACE(\n REPLACE(\n REPLACE(\n REPLACE(\n RawAmount, '(', '-'\n ), ')', ''\n ), '$', ''\n ), ',', ''\n ), ' ', ''\n ) AS DECIMAL(18,2)\n ) AS CleanAmount\nFROM SalesRaw;<\/code><\/pre>\n\n\n\nId RawAmount CleanAmount
-- --------- -----------
1 1,200 1200
2 $450.75 450.75
3 3000 3000
4 12abc34 null
5 (200) -200
6 null null
7 7 500 7500 <\/pre>\n\n\n\nnull<\/code>, which is an unknown value, so we wouldn’t be able to clean it anyway (there’s nothing to clean). The other value contains a bunch of letters in the middle, which suggests that it’s either not a number, or it uses a different base (e.g., base-16), which means that it would need more than just a bit of a clean.<\/p>\n\n\n\nUsing
TRANSLATE()<\/code> to Clean the Data<\/h3>\n\n\n\nTRANSLATE()<\/code> to replace some of the REPLACE()<\/code> functions (although we still use one REPLACE()<\/code> function):<\/p>\n\n\n\nSELECT \n Id,\n RawAmount,\n CleanAmount = TRY_CAST(\n REPLACE(\n TRANSLATE(RawAmount, '()$, ', '-' + REPLICATE(' ', 4)),\n ' ', ''\n ) AS DECIMAL(18,2)\n )\nFROM SalesRaw;<\/code><\/pre>\n\n\n\nId RawAmount CleanAmount
-- --------- -----------
1 1,200 1200
2 $450.75 450.75
3 3000 3000
4 12abc34 null
5 (200) -200
6 null null
7 7 500 7500 <\/pre>\n\n\n\nTakeaways<\/h2>\n\n\n\n
\n
REPLACE()<\/code> (and\/or TRANSLATE()<\/code> if you prefer) to strip out known problem characters.<\/li>\n\n\n\nTRY_CAST()<\/code> or TRY_CONVERT()<\/code> to avoid failures when bad data slips through (and set the bad values to null<\/code>).<\/li>\n\n\n\n