Replace % with empty string in text field and convert to numeric

I'm importing data from a tab delimited file into SQL Server with Bulk Insert.

Two of the fields are percentages formatted as "100%" or "95.83%", ..., but I have to import those columns into my temp table as nvarchar().

However, when I push this data into my production table, I want to do so as a numeric value.  What syntax can I use to convert this text value to numeric values like 1.0000 and 0.9583?

Is there a better syntax than:

Cast(LEFT(Dashboard_SSQ_Score_Percentage, LEN(Dashboard_SSQ_Score_Percentage)-1) as float)/100 as SSQ_Pct
LVL 50
Dale FyeAsked:
Who is Participating?
 
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
You could use REPLACE as an option to get rid of the percent sign as well.

DECLARE @Test AS NVARCHAR(10)

SET @Test = '100%'

SELECT CAST(REPLACE(@Test, '%', '') AS DECIMAL(9,4)) / 100

SET @Test = '95.86%'

SELECT CAST(REPLACE(@Test, '%', '') AS DECIMAL(9,4)) / 100

Open in new window

0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Looks like a winner as long as you can make an assumption that the last character will always be the % sign, and there are no values 1000% or greater that have thousands separators.

CREATE TABLE #tmp (val_in NVARCHAR(10), val_out DECIMAL(19,4))  

INSERT INTO #tmp (val_in)
VALUES ('100%'), ('95.83%')

UPDATE #tmp
SET val_out = Cast(LEFT(val_in, LEN(val_in)-1) as float)/100

SELECT * FROM #tmp

Open in new window

val_in.jpg
0
 
Dale FyeAuthor Commented:
Thanks, guys.

I absolutely hate writing code which demands that a particular character be in a specific place in a string, especially when I'm getting the data from an outside source.  I really wanted to go with Replace, but incorrectly assumed that the % would be viewed as a wildcard rather than a literal, so I tried it with '[%]' and that did not work; I didn't even try it without the brackets.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.