Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

SQL Server syntax - remove non-numeric except

Hello

How can I remove non numeric characters like '$', ',', '=', from '=$1,230.00' so that it can become 1230.00.

Basically trying to remove all non-numeric characters except the decimal point from a string and have the 1230.00 appear as a decimal (18,5)

Would be nice to have a function that I can call
0
barkome
Asked:
barkome
  • 2
  • 2
  • 2
  • +2
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
making a function is simple, but may not be the most efficient usage, especially for multi-row queries

replace(replace(replace(your_value_or_field, '$',''),',',''),' ','')

would be a starter. you can add more replace expressions as needef
0
 
funwithdotnetCommented:
Usually:
select cast(cast('$123,440.57' as money) as float)
or
select cast(cast('$123,440.57' as money) as decimal(18,2))

... works for me.
0
 
barkomeAuthor Commented:
It works when the value ('=$1,230.00') is hard coded, but doesn't when i use the column name (Col_A).

getting an error - Error converting data type nvarchar to numeric.

This is the syntax:

 CONVERT(DECIMAL(18,5), REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Total_Amount ,',',''), '$', '' ), '(', '-'), ')', ''), '=', '')) Total

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
funwithdotnetCommented:
To remove the = sign ...
SELECT CAST(CAST(REPLACE('=$1,230.00','=','') as money) as float)
SELECT CAST(CAST(REPLACE('=$1,230.00','=','') as money) as decimal(18,2))
1
 
Dung DinhDBA and Business Intelligence DeveloperCommented:
Maybe, the Col_A column contains other characters that you have not declared to replace them.

If you are using SQL Server 2012, I suggest the following steps:

Step 1- Detect any characters that will need to be replaced.

WITH cte
As
(          SELECT
                 Col_A,
                 TRY_PARSE(Col_A as money) AS Converted_Col_A
            FROM <your table>
)
SELECT DISTINCT Col_A
FROM cte
WHERE Converted_Col_A IS NULL

From the result, you can find out different characters in Col_A  and then try to replace them.
0
 
barkomeAuthor Commented:
Gave me what I was expecting but came up with an error:

[Execute SQL Task] Error: Executing the query "EXEC SP 3" failed with the following error: "Cannot convert a char value to money. The char value has incorrect syntax.".
0
 
Dung DinhDBA and Business Intelligence DeveloperCommented:
Hmm... Could you post full code here?
0
 
Scott PletcherSenior DBACommented:
Here's a function to strip all chars other than 0-9 and .


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[StripNonnumericChars] (
      @string varchar(200)
      )
RETURNS varchar(200)
WITH RETURNS NULL ON NULL INPUT
AS
--Author: Scott Pletcher.
BEGIN
DECLARE @byte int
WHILE 1 = 1
BEGIN
      SET @byte = PATINDEX('%[^0-9.]%', @string)
      IF @byte = 0
            BREAK
      SET @string = STUFF(@string, @byte, 1, '')
END --WHIILE
RETURN @string
END /*FUNCTION*/
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now