Matt is correct... he's beat the snare right off that drum... and guess what? He's right! Matt and I have done a huge amout of testing and using a RegEx "xp" in SQL Server 2000 is definitely a great way to go...

On the off chance that you have a DBA that refuses to allow a "non-MS XP", there's a fairly easy way to do it... the basis of the method is covered in the same thread that Matt sited. But, for everyone's convenience, here it is...

First, you need a Tally table... it's nothing more than a table with a single column of well indexed sequential numbers... you can make it as a Temp Table or, better yet, add it to your database as a permanent table. It's got a lot of uses so I recommend the latter. Here's how to make one...

--===== Create and populate the Tally table on the fly

SELECT TOP 11000 --equates to more than 30 years of dates

IDENTITY(INT,1,1) AS N

INTO dbo.Tally

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance

ALTER TABLE dbo.Tally

ADD CONSTRAINT PK_Tally_N

PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Allow the general public to use it

GRANT SELECT ON dbo.Tally TO PUBLIC

Next, you'll need a function... here's a pretty useful generic function that you can "program" to return your desired result...

CREATE FUNCTION dbo.fnLeaveOnly

/***********************************************************************************

Purpose:

This function accepts a string, the "LIKE" pattern that all characters must match

to be "kept", and a value to return if a NULL is the result of the function.

-- Jeff Moden

***********************************************************************************/

--===== Define the I/O Parameters

(

@String VARCHAR(8000), --String to be cleaned

@CharPattern VARCHAR(100), --Pattern a character must meet to keep

@NullValue VARCHAR(100) --Return this if a NULL is the result

)

RETURNS VARCHAR(8000) --The "cleaned" string

AS

BEGIN

--===== Declare the return variable

DECLARE @Return VARCHAR(8000)

--===== Clean the string leaving only what's in the character pattern

SELECT @Return = ISNULL(@Return,'')+SUBSTRING(@String,N,1)

FROM dbo.Tally

WHERE N <= LEN(@String)

AND SUBSTRING(@String,N,1) LIKE @CharPattern

--===== Return the "Cleaned" string substituting the null value if result is null

RETURN ISNULL(@Return,@NullValue)

END

Now, let's setup a test with all the values you stated and see what happens... do notice the "pattern" used...

DECLARE @yourtable TABLE (StarRating VARCHAR(100))

INSERT INTO @yourtable (StarRating)

SELECT '0' UNION ALL

SELECT '1' UNION ALL

SELECT '1.5' UNION ALL

SELECT 'NA' UNION ALL

SELECT '2' UNION ALL

SELECT '2 STAR' UNION ALL

SELECT ' ' UNION ALL

SELECT '3.00' UNION ALL

SELECT '4-STAR' UNION ALL

SELECT '4' UNION ALL

SELECT NULL UNION ALL

SELECT '~`!@#$%^&*()-_=+\|[{]};:''", /?a1_b2C3$'

SELECT StarRating AS Original,

dbo.fnLeaveOnly(StarRating,'[0-9.]',0)

FROM @yourtable

Result:

(12 row(s) affected)

Original Cleaned

----------------------------------------- ----------

0 0

1 1

1.5 1.5

NA 0

2 2

2 STAR 2

0

3.00 3.00

4-STAR 4

4 4

NULL 0

~`!@#$%^&*()-_=+\|[{]};:'", /?a1_b2C3$ .123

(12 row(s) affected)

Hope that helps...