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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply