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

Can you post some sample from your column. Do you have alpha-numeric characters only or any special characters?

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

Select allOpen 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.,

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

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;

Select allOpen 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.

As follow up, I tested both and ran the following 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.

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

Select allOpen 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
*/

Select allOpen in new window

Sharath, I believe ($5.62) should become  -5.62

Paul, why ($5.62) should be -5.62?

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.

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

Select allOpen in new window

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.)

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

Day job is getting in the way...will run tonight!

no haste at all, but much appreciated. thank you.