Cast / Temp field conversion from nvarchar to int failing - any ideas ?

Question

Post reply

Cast / Temp field conversion from nvarchar to int failing - any ideas ?

isuckatsql

SSChampion

Points: 10381
More actions
October 22, 2011 at 7:24 am

#245642

begin
declare @tempian int;
SELECT top 1 ID, secondaryemailaddress,
ltrim(rtrim(SUBSTRING(secondaryemailaddress,3,3))) as '@tempian'
--CAST ((select top 1 ltrim(rtrim(SUBSTRING(secondaryemailaddress,3,3))) from Profiles) as int) as @tempian
FROM profiles
where secondaryemailaddress like '%years%'
end
begin
update Profiles
set Experience = @tempian
--CAST (select top 1 ltrim(rtrim(SUBSTRING(secondaryemailaddress,3,3))) from Profiles where secondaryemailaddress like '%years%' as int
where secondaryemailaddress like '%years%'
end

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

What exactly are you trying to do?

If there's a conversion error you might use a different data type than int for @tempian.

Other than that it looks like there's a general syntax issue:

you can't use

CAST ((select top 1 ltrim(rtrim(SUBSTRING(secondaryemailaddress,3,3))) from Profiles) as int) as @tempian

Instead, use

SELECT @tempian = CAST ((select top 1 ltrim(rtrim(SUBSTRING(secondaryemailaddress,3,3))) from Profiles) as int)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Yes... it simply means that you're selecting data for conversion that cannot be converted. Put another way, you're selecting data for conversion that doesn't look like an INT value.

Please see the following article for how to detect and filter such values...

http://www.sqlservercentral.com/articles/IsNumeric/71512/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

isuckatsql SSChampion Points: 10381 More actions · Answer 3

": 8 years" This is the data in my generic field secondaryemail address - nvarchar(100)

I want to pull out the number and update the Experience column, whch is an integer.

I managed to get it working in SSIS but it takes 20 seconds to process one record!

Based on the actual execution plan, the time is being used up by updating the index. How can i leave the index update until after i have processed ALL my 4 Million records?

Is my best option to delete the index and rebuild after i have done my updates?

Regex reg = new Regex(@"(?<=Total years experience.*?)\b\S{1,}\b(?=.*?Job Categories)"); This is my original Regex code, which extracted # of years and the word years into 2 seperate columns, and i had no update issues, but it was slow!

100k records in 2 hours!

Regex reg = new Regex(@"(?<=Total years experience)\b.*\b(?=Job Categories)"); This is my current Regex code, which is MUCH faster, 100k records in 3 minutes but places the data in the following format ": 8 years" so i must parse out the full colon, spaces, and the word years, then convert to integer.

Both partially work, but are not quite what i want!

I have attached the execution plan from my SQL code.

isuckatsql SSChampion Points: 10381 More actions · Answer 4

isuckatsql

SSChampion

Points: 10381

October 22, 2011 at 2:55 pm

#1398401

Fixed 🙂

Thanks for the assistance!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Cool! Two way street here, though... please tell us what you did to fix it?

Also, where did all that Regex stuff come from? It wasn't in your original post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

isuckatsql SSChampion Points: 10381 More actions · Answer 6

The trim was not working because the first character was a tab.

select ASCII(substring(IanVarchar,1,1)) from profiles

Once i determined the trim was not working, i substringed the values i needed and the conversion worked fine 🙂