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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply