I need to parse out only the middle string from a whole string using a select statement. I'm able to accomplish this in a loop only after I've selected the whole string. However, it doesn't serve my purpose to do it this way.



here's the code that I need to be in a select

set @shortcellname = substring(@description,len(SUBSTRING(@description, 1, CHARINDEX('_',@description)) )+1,len(SUBSTRING(@description, 1, CHARINDEX('.',@description)) )-len(SUBSTRING(@description, 1, CHARINDEX('_',@description)) ))
  set @shortcellname = substring(@shortcellname, 1, charindex('_',@shortcellname)-1)
  set @shortcellname = replace(@shortcellname, ' ',' ')

the first set works fine - I'm having a problem combining the 1 and 2nd sets into the following:

substring(substring(description,len(SUBSTRING(desc ription, 1, CHARINDEX('_',description)) )+1,len(SUBSTRING(description, 1, CHARINDEX('.',description)) )-len(SUBSTRING(description, 1, CHARINDEX('_',description)) )),1, charindex('_',(substring(description,len(SUBSTRING (description, 1, CHARINDEX('_',description)) )+1,len(SUBSTRING(description, 1, CHARINDEX('.',description)) )-len(SUBSTRING(description, 1, CHARINDEX('_',description)) )))-1))



I get the following error:

Syntax error converting the nvarchar value 'sample string from description column.' to a column of data type int.



anyone what to attempt this resolution

I tried to make sense of what you are trying to accomplish.
The first sql statement does not work.
When using 'sample string from description column.' for @description.
Msg 536, Level 16, State 5, Line 16
Invalid length parameter passed to the SUBSTRING function.

Can you explain a little more about what you are trying to accomplish?

Adam Gossage
Lake Wylie, SC, USA

Try this function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

Cole,

Can you post it explaining what you are trying to do with some example string value, mentioning the whole process of you accomplisment? So that it is easy for one to understand, than going through a lot of substring and charindex which will end up getting too complicated for one to look into.

cheers.

_________________________
- Vijay G
Strive for Perfection