x

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'
more ▼

asked Jan 07 '11 at 07:54 PM in Default

Charles Broadfoot gravatar image

Charles Broadfoot
21 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first
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))))
more ▼

answered Jan 07 '11 at 09:04 PM

Scot Hauder gravatar image

Scot Hauder
5.9k 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)
10|1200 characters needed characters left

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

answered Jan 07 '11 at 08:03 PM

Tim gravatar image

Tim
35.5k 32 40 138

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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x977
x362
x55

asked: Jan 07 '11 at 07:54 PM

Seen: 873 times

Last Updated: Jan 07 '11 at 08:05 PM