asked
Selecting column with multiple functions
I'm trying to select a column with multiple functions and I dont know how. Lets say that my column's data returns data with values such as 'Foo_RockStar.Complete' and what I want to extract is the 'RockStar'. There could be lots of pre-fixes such as the 'Foo_' and lots of endings such as the '.Complete', so my initial query looked somthing like.... SELECT SUBSTRING([column1],4,20) REPLACE([column1],'.Complete','') AS [DesiredText] FROM MYTable WHERE [column1] like '%.Complete' The Desired outcome would be somthing like 'RockStar'
To answer your question about syntax of your SELECT statement, it would be something more like SELECT replace(SUBSTRING(@row,4,20),'.Complete','') AS [DesiredText] ... Are you looking for a solution that would account for the multiple prefixes and endings or does the query above meet your needs? This should work for what you need just replace @row with your column name in the select statement, to test the statement I had to create a variable to pass to it.: DECLARE @row varchar(50) SET @row = 'Foo_RockStar.Complete' SELECT SUBSTRING(@row,((CHARINDEX('_',@row)+1)),(CHARINDEX('.',@row)-(CHARINDEX('_',@row)+1)))
I'm actualy looking for the ability to use more than one function on the same column. multiple prefixes and multiple endings exists with varing lengths
DECLARE @COLUMN varchar(50) SET @COLUMN = 'prefix_Foo_RockStar.Complete.suffix' SELECT SUBSTRING(@COLUMN,2+LEN(@COLUMN)-CHARINDEX('_',REVERSE(@COLUMN)) ,CHARINDEX('.',@COLUMN)-(2+LEN(@COLUMN)-CHARINDEX('_',REVERSE(@COLUMN))))
I learn a lot here myself and enjoy these challenges. Although the real answer to this is question is to normalize the table and create a separate column for YOURVALUE
Agreed, he has some very bad data inputs going on. Many times though we have no control over the data we manage. I just got through stepping through you code, once you break the individual segments down it is pretty simple logic. I will be adding this little snippet of code to my code bank as I am sure I will need something like this at some point in the future. Tonight is really the first time I have used charindex. I have known about it and known its usage but haven't ever used it before.
I've seen this a lot--knowledge built into a string or invoice number/customer number and you're right, we always arrive after that fact and can't change the schema
@Scot, curious about something for my own knowledge and education. To get the number of characters to the period(.) you chose to use PATINDEX instead of CHARINDEX in that situation only. Why go with PATINDEX? Wouldn't CHARINDEX still achieve the same goal? I figure you had a reason, I am just not that familiar with the functions. I tested your solution with PATINDEX and CHARINDEX and got the same result. I figure there is a gotcha I am not aware of.
PATINDEX was part of a previous solution when I wasn't clear of the OP's requirement. As you mention, it is suffice to use CHARINDEX and I actually changed it to that almost immediately after posting which makes me think you saw the PATINDEX in your email...
