May 30, 2019 at 10:47 pm
I get that error when i run my code against a very large table.
Most Prod_IDs have two dashes. I want to pull the value between the dashes. This code works, until it hits an odd ball part in the table where there is only 1 dash.
Not sure how to write the case statement to get around it, in this case, i don't care about parts without two dashes, is there a way to turn off warnings, similar to ANSI warnings for divide by 0?
thanks
SUBSTRING(PROD_id,CHARINDEX('-',PROD_ID)+1,(((LEN(PROD_ID))-CHARINDEX('-', REVERSE(PROD_id)))-CHARINDEX('-',pROD_id))) AS Prod_Base, May 30, 2019 at 10:50 pm
I get that error when i run my code against a very large table. Most Prod_IDs have two dashes. I want to pull the value between the dashes. This code works, until it hits an odd ball part in the table where there is only 1 dash. Not sure how to write the case statement to get around it, in this case, i don't care about parts without two dashes, is there a way to turn off warnings, similar to ANSI warnings for divide by 0? thanks
SUBSTRING(PROD_id,CHARINDEX('-',PROD_ID)+1,(((LEN(PROD_ID))-CHARINDEX('-', REVERSE(PROD_id)))-CHARINDEX('-',pROD_id))) AS Prod_Base,
As you "don't care" about these cases, why not exclude them in your WHERE clause?
Alternatively, handle them using an IIF() condition.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 30, 2019 at 10:54 pm
there are thousands . . . . can I exclude them in the where clause by using
WHERE
LEN(PROD_ID) - LEN(REPLACE(PROD_ID, '-', '')) > 1
??
May 30, 2019 at 10:57 pm
well, crap on a cracker, that worked!! thanks for pointing me in the right direction besides south!!
May 30, 2019 at 11:00 pm
there are thousands . . . . can I exclude them in the where clause by using WHERE LEN(PROD_ID) - LEN(REPLACE(PROD_ID, '-', '')) > 1 ??
That's how I'd do it. Well done on figuring it out.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply