I need to replace characters in one of my columns in SQL Server

Hi Experts,
I have a Varchar column in one of my SQL Server tables, that I would like to convert to a money column.  I cannot do that because currently that column has some records with characters that cannot be converted to money.  How can I remove unwanted characters so that I may convert this column to money data type?  Thanks in advance

mrotor
mainrotorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SharathData EngineerCommented:
Can you post some sample from your column. Do you have alpha-numeric characters only or any special characters?
0
coreconceptsCommented:
Need some examples of what's in the columns - there's multiple ways to accomplish this.  Also are you talking about SELECTING something so you can view it as money datatype or are you referring to updating tables?  Here's an example of the REPLACE function.  Say you have the following

MyColumn
----------------
ABC41.09
ABC7.99

And you use the following:

SELECT
	CAST(REPLACE(MyColumn, 'ABC', '') AS MONEY)
FROM <whateveryourtablenameis> As MyMoneyColumn

Open in new window


You will get the following :

MyMoneyColumn
--------------------------
41.09
7.99


But we need samples, so we can determine if it's a fixed length character and where it lies in the string etc.,
0
mainrotorAuthor Commented:
Here is a sample of my Data.  They actually have double quotes in the colum.  Some values a blank as well.

Sample Data
"$1,234.13"
125.5
$-7.85
($5.62)

How can convert this to a money column?

Coreconcepts, i will try your suggestion now.

thanks
mrotor
0
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

coreconceptsCommented:
Hi mainrotor,

    Not sure about the negative values yet - but my suggestion is you create a UDF as follows:

CREATE FUNCTION DBO.MyFunction(@MyVar As VARCHAR(24))
RETURNS MONEY
AS
BEGIN
--Find Where The Numeric Values Exist...
DECLARE @start INT = ( PATINDEX('%[0-9]%', @MyVar) )
DECLARE @end INT = ( PATINDEX('%[0-9]%', (REVERSE(@MyVar))))
DECLARE @length INT = LEN(@MyVar)

--Cut out the rest, cast as money...

RETURN
(
CAST(SUBSTRING(@MyVar, @start, (@length - @start - @end + 2)) As Money)
)
END;

Open in new window


Then select from it as follows:

SELECT
<your_column>
, dbo.MyFunction(<yourcolumn>)
FROM <yourtablename>

What you're doing in the function is using the PATINDEX function to find where the first and last occurrence of a number is...  I'd have to think about how to alter it to accommodate for a negative... given that you have 2 separate ways of showing negative (using the - sign as well as encapsulating in "()").  

The way I scripted the above will ignore your negatives.  Need to think about how to address but this is a solid start.
0
Lawrence BarnesCommented:
A function is very cool...but if you want something quick and dirty here's an example of cleaning it up with REPLACE and then casting as a decimal.  This also converts a number surrounded by ()'s to a negative.

CREATE TABLE #tmp (DirtyVAR VARCHAR(10))
INSERT INTO #tmp (DirtyVAR)
VALUES('"1,234.13"'),('125.5'),('$-7.85'),('($5.62)')

SELECT * FROM #tmp

SELECT t.DirtyVar
	, CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CASE WHEN t.DirtyVAR LIKE '%(%)%' THEN '-' + t.DirtyVAR ELSE t.DirtyVAR END,'"',''),',',''),'$',''),')',''),'(','') as DECIMAL(8,2)) as CleanedVar
FROM #tmp t

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
coreconceptsCommented:
I didn't test it lvbarnes, but if your solution works this can be ignored.  Here's the function though that accounts for negatives using "-" (X.XX) or ($X.XX) format - so as long as it's a number it'll work:

CREATE FUNCTION DBO.Get_Money_From_String(@MyVar As VARCHAR(24))
RETURNS MONEY
AS
BEGIN
--Find Where The Numeric Values Exist...
DECLARE @start INT = ( PATINDEX('%[0-9]%', @MyVar) )
DECLARE @end INT = ( PATINDEX('%[0-9]%', (REVERSE(@MyVar))))
DECLARE @length INT = LEN(@MyVar)

--Cut out the rest, cast as money...

/*does the string show a negative sign?*/
IF SUBSTRING(@MyVar, @start - 1, 1) = '-'
BEGIN
RETURN
(
CAST(SUBSTRING(@MyVar, @start, (@length - @start - @end + 2)) As Money)
) * -1	
END	

ELSE
--is the number DIRECTLY surrounded by ()
IF SUBSTRING(@MyVar, @start -2, 1) = '(' 
AND SUBSTRING(REVERSE(@MyVar), @end -1, 1) = ')' 
BEGIN
RETURN
(
CAST(SUBSTRING(@MyVar, @start, (@length - @start - @end + 2)) As Money)
) * -1	
END	

--is the number INDIRECTLY surrounded by ()
IF SUBSTRING(@MyVar, @start -1, 1) = '(' 
AND SUBSTRING(REVERSE(@MyVar), @end -1, 1) = ')' 
BEGIN
RETURN
(
CAST(SUBSTRING(@MyVar, @start, (@length - @start - @end + 2)) As Money)
) * -1	
END	

--Otherwise Just Return the number
RETURN
(
CAST(SUBSTRING(@MyVar, @start, (@length - @start - @end + 2)) As Money)
)
END;

Open in new window


You call it the same way as before in your select statement:

SELECT
        DBO.Get_Money_From_String ( <yourcolumn>)
FROM <yourtable>
0
coreconceptsCommented:
As follow up, I tested both and ran the following execution plan:

execution plan
shocked that they showed 50/50 cost.  Very cool - I like using the function to replace the logic and abstract, though yours is much easier to follow.  I would suggest encapsulating that logic in a function and save it for later so you can address similar problems in the future.
0
PortletPaulfreelancerCommented:
well, I had a break and much has happened, plus I hadn't anticipated the brackets, but for the record here's something I came up with largely because I had assumed much dirtier data to deal with... (another function approach)
    CREATE TABLE YourTable
    	([AvarcharOfMoney] varchar(100))
    ;
    	
    INSERT INTO YourTable
    	([AvarcharOfMoney])
    VALUES
    	('x123.12'),
    	('rubbish'),
    	('234.56'),
    	('-234.56'),
    	('456.78 - 234.56'),
    	('$44.33'),
    	('2013-09-01 12:13:14'),
    	('long string that doesn''t belong here but has $55.66 in it')
    ;
    
    
    CREATE FUNCTION [dbo].[ExtractDecimalFromString] (@Temp varchar(1000))
    RETURNS varchar(1000)
    AS
            BEGIN
                    DECLARE @dash AS int
                    DECLARE @dot  AS int
    
                    WHILE PATINDEX('%[^0-9.\-]%', @Temp) > 0
                            SET @Temp = STUFF(@Temp, PATINDEX('%[^0-9.\-]%', @Temp), 1, '')
    
                    SET @dash = LEN(REPLACE(@temp, '-', ''))
                    SET @dot  = LEN(REPLACE(@temp, '.', ''))
    
                    IF LEN(@temp) - @dash > 1 OR LEN(@temp) - @dot > 1
                            SET @temp = ''
    
                    RETURN @Temp
            END
    ;
    

**Query 1**:

    /* get A decimal or integer value from a varchar */
    
    SELECT
          AvarcharOfMoney
        , dbo.ExtractDecimalFromString(AvarcharOfMoney)
    FROM YourTable
    
    /* NB: if not A number it returns an "empty string" not a NULL */
    
    

**[Results][2]**:
    
    |                                           AVARCHAROFMONEY | COLUMN_1 |
    |-----------------------------------------------------------|----------|
    |                                                   x123.12 |   123.12 |
    |                                                   rubbish |          |
    |                                                    234.56 |   234.56 |
    |                                                   -234.56 |  -234.56 |
    |                                           456.78 - 234.56 |          |
    |                                                    $44.33 |    44.33 |
    |                                       2013-09-01 12:13:14 |          |
    | long string that doesn't belong here but has $55.66 in it |    55.66 |



  [1]: http://sqlfiddle.com/#!3/8887b/2

Open in new window

0
SharathData EngineerCommented:
Can you try creating this UDF.
create function dbo.fnOnlyNumbers(@MyColumn nvarchar(1000)) returns nvarchar(1000) as
begin
DECLARE @Col nvarchar(1000)
    SET @Col=''
 SELECT @Col = @Col + CASE WHEN number LIKE '[-.0-9]' THEN number ELSE '' END 
   FROM (SELECT SUBSTRING(@MyColumn,number,1) AS number 
           FROM (SELECT number FROM master..spt_values 
                  WHERE type = 'p' AND number BETWEEN 1 AND LEN(@MyColumn)) as t) as t 
 return convert(money,replace(replace(@Col,'(',''),')',''))
end

Open in new window


You can use the UDF in your query.
declare @table table(name varchar(2000))
Insert into @table values('"$1,234.13"')
Insert into @table values('125.5')
Insert into @table values('$-7.85')
Insert into @table values('($5.62)')

SELECT name,dbo.fnOnlyNumbers(name) Modified_Name FROM @table
/*
name	Modified_Name
"$1,234.13"	1234.13
125.5	125.50
$-7.85	-7.85
($5.62)	5.62
*/

Open in new window

0
PortletPaulfreelancerCommented:
Sharath, I believe ($5.62) should become  -5.62
0
SharathData EngineerCommented:
Paul, why ($5.62) should be -5.62?
0
PortletPaulfreelancerCommented:
It is an accounting method for marking numbers as negative.
Used also in Excel where I suspect those numbers come from.

So if you see a bank account with many (99999.99) that is not a good thing.
0
PortletPaulfreelancerCommented:
Well here's an update to my approach that handles the supplied sample and some others:
    
|                                           AVARCHAROFMONEY | COLUMN_1 |
|-----------------------------------------------------------|----------|

supplied sample
|                                               "$1,234.13" |  1234.13 |
|                                                     125.5 |    125.5 |
|                                                    $-7.85 |    -7.85 |
|                                                   ($5.62) |    -5.62 |

other
|                                                   x123.12 |   123.12 |
|                                                   rubbish |          |
|                                                    234.56 |   234.56 |
|                                                   -234.56 |  -234.56 |
|                                           456.78 - 234.56 |          |
|                                                    $44.33 |    44.33 |
|                                       2013-09-01 12:13:14 |          |
| long string that doesn't belong here but has $55.66 in it |    55.66 |

-- --------------   FUNCTION  ----------------------

CREATE FUNCTION [dbo].[ExtractDecimalFromString] (@Temp varchar(1000))
RETURNS varchar(1000)
AS
        BEGIN
                DECLARE @dash AS int
                DECLARE @dot  AS int

                SET @temp = REPLACE(@temp, '(', '-') /* for accounting treatment of negatives */

                WHILE PATINDEX('%[^0-9.\-]%', @Temp) > 0
                        SET @Temp = STUFF(@Temp, PATINDEX('%[^0-9.\-]%', @Temp), 1, '')

                SET @dash = LEN(REPLACE(@temp, '-', ''))
                SET @dot  = LEN(REPLACE(@temp, '.', ''))

                IF LEN(@temp) - @dash > 1 OR LEN(@temp) - @dot > 1
                        SET @temp = ''

                RETURN @Temp
        END
;

-- --------------   USAGE  ----------------------

/* get A decimal or integer value from a varchar */

SELECT
      AvarcharOfMoney
    , dbo.ExtractDecimalFromString(AvarcharOfMoney)
FROM YourTable

/* NB: if not A number it returns an "empty string" not a NULL */
    
http://sqlfiddle.com/#!3/c5d5a/1

Open in new window

0
Lawrence BarnesCommented:
I've seen some nice functions written here.  The replace method I posted (way up there) is fast too and great for restrictive environments.  I'll run a time test later today over a few million records and see what's fastest.  (Unless someone beats me to it.)
0
PortletPaulfreelancerCommented:
What were the results? It would be interesting for us too.

not that I'm expecting scalar functions to win in a speed test - just inteested
0
Lawrence BarnesCommented:
Day job is getting in the way...will run tonight!
0
PortletPaulfreelancerCommented:
no haste at all, but much appreciated. thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.