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]
WHERE [column1] like '%.Complete'

The Desired outcome would be somthing like 'RockStar'

more ▼

asked Jan 07, 2011 at 07:54 PM in Default

avatar image

Charles Broadfoot
21 1 1 1

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

2 answers: sort voted first
 DECLARE @COLUMN varchar(50)
 SET @COLUMN = 'prefix_Foo_RockStar.Complete.suffix'
more ▼

answered Jan 07, 2011 at 09:04 PM

avatar image

Scot Hauder
6.5k 13 16 22

@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, 2011 at 09:08 PM Tim

Try this one

Jan 07, 2011 at 09:11 PM Scot Hauder

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

Jan 07, 2011 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, 2011 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, 2011 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, 2011 at 08:03 PM

avatar image

40.9k 39 94 168

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, 2011 at 08:06 PM Charles Broadfoot

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

Jan 07, 2011 at 08:12 PM Tim

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

Jan 07, 2011 at 08:17 PM Charles Broadfoot

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

Jan 07, 2011 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, 2011 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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Jan 07, 2011 at 07:54 PM

Seen: 1198 times

Last Updated: Jan 07, 2011 at 08:05 PM

Copyright 2018 Redgate Software. Privacy Policy