Solved

SQL Server syntax - remove non-numeric except

Posted on 2016-07-24
8
94 Views
Last Modified: 2016-07-25
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
Comment
Question by:barkome
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41726619
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
 
LVL 12

Expert Comment

by:funwithdotnet
ID: 41726916
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
 

Author Comment

by:barkome
ID: 41726930
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Accepted Solution

by:
funwithdotnet earned 167 total points
ID: 41726953
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
 
LVL 7

Assisted Solution

by:Dung Dinh
Dung Dinh earned 167 total points
ID: 41726989
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
 

Author Comment

by:barkome
ID: 41726996
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
 
LVL 7

Expert Comment

by:Dung Dinh
ID: 41727140
Hmm... Could you post full code here?
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 166 total points
ID: 41727736
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Table where row act as column 11 70
SP result not being displayed 5 42
Trigger C# code inside the SQL Server 6 33
Delete old Sharepoint backups 2 21
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question