Selecting column with multiple functions

Question

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 'FooRockStar.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'

Answer 1

0

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))))

answered Jan 07 '11 at 09:04 PM

Scot Hauder
5.7k ● 13 ● 15 ● 18

@scot Your solution like mine above only works when the column has a single prefix and suffix, however his column can have multiple prefixes and suffixes. IE - something_YOURVALUE.ending or somthing_something_somthing_YOURVALUE.ending.ending

Any ideas?

Jan 07 '11 at 09:08 PM Tim

Try this one

Jan 07 '11 at 09:11 PM Scot Hauder

Great Job @Scot +1. I love trying to help someone and learning something new myself.

Jan 07 '11 at 09:18 PM Tim

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

Jan 07 '11 at 09:23 PM Scot Hauder

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.

Jan 07 '11 at 09:29 PM Tim

(comments are locked)

Answer 2

0

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)))

answered Jan 07 '11 at 08:03 PM

Tim
31.5k ● 20 ● 30 ● 116

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

Jan 07 '11 at 08:06 PM Charles Broadfoot

Does the prefix always end with _ and the ending come after a (.)period?

Jan 07 '11 at 08:12 PM Tim

yes, but there could be more than one of each in the name

Jan 07 '11 at 08:17 PM Charles Broadfoot

so like first index of _ and last index of . would work

Jan 07 '11 at 08:17 PM Charles Broadfoot

So every scenario would be something_YOURVALUE.ending Where 'something' could be of various lengths and 'ending' could be one of many words.

Jan 07 '11 at 08:21 PM Tim

(comments are locked)

Selecting column with multiple functions

Follow this question

Related Questions