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
it could be something_YOURVALUE.ending or somthing_something_somthing_YOURVALUE.ending.ending
That is just bad data input. I've got to head to bed so I will be signing off. Is the process you are trying to write to clean this data up or for feeding into an app. The solution I gave above will only work for something_YOURVALUE.ending, but if you are cleaning the data up, as you extract it to a temp table or staging table you could just loop through it a few times. I am sure some of the SQL Developer that will be on here over the weekend or on Monday will have some slick solutions for you.
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.
