TSQL - Conditioning a string

Hi Experts,

I have this:
[Description]

The value in the field is:

2015 Allied A Membership Renewal What ever comes after

I would like to have just
2015 Allied A Membership Renewal
appear cutting off anything after "Membership Renewal"

So something like:
Left([Despcription], "Membership Renewal")

keeping in mind that anything before "Membership Renewal" should also be included like:

2015 Book Membership Renewal
2015 Water solutions Membership Renewal
2015 Growers Membership Renewal

They will all have "Membership Renewal"

Please help and thanks...
Amour22015Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Easy.  Use CHARINDEX to get the starting character position of 'Membership Renewal', then get the left most characters of the string, that position plus the length of 'Membership Renewal' minus one.
Declare @str varchar(100) = '2015 Allied A Membership Renewal What ever comes after'

-- Get the starting character position of 'Membership Renewal'
SELECT CHARINDEX('Membership Renewal', @str)

-- And get the LEFT of that 
SELECT CASE CHARINDEX('Membership Renewal', @str)
	WHEN 0 THEN ''
	ELSE LEFT(@str, CHARINDEX('Membership Renewal', @str) + LEN('Membership Renewal') - 1) END

Open in new window

0
Amour22015Author Commented:
Ok,

Now I am confused.

Where is [Description]?

So you are saying to do this:
Declare @str varchar(100) = [Description]

-- Get the starting character position of 'Membership Renewal'
SELECT CHARINDEX('Membership Renewal', @str)

-- And get the LEFT of that 
SELECT CASE CHARINDEX('Membership Renewal', @str)
	WHEN 0 THEN ''
	ELSE LEFT(@str, CHARINDEX('Membership Renewal', @str) + LEN('Membership Renewal') - 1) END

Open in new window


I have [Description] on a MS Access Report.

Please help and thanks...
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Where is [Description]?
The code I provided was written in SSMS just to test that it met your requirements, so you can copy it into SSMS, execute, and verify.  Since I'm not connected to your data source, I couldn't do that for any of your tables and columns such as [Description], so you'll have to modify the code to fit your data source.

Something like <air code>
SELECT CASE CHARINDEX('Membership Renewal', [Description])
   WHEN 0 THEN ''
   ELSE LEFT([Description], CHARINDEX('Membership Renewal', [Description]) + LEN('Membership Renewal') - 1) END as alias_name_goes_here
FROM your_table

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I have [Description] on a MS Access Report.
Okay.  Having something on a report may not be the same as the column name in the table, so you'll have to look at the report's Record Source and determine the table and query names.

If you want this to display solely in Access, then that's an Access VBA / expression question.
The zones are SQL Server and not Access, so I assumed SQL Server.
0
Amour22015Author Commented:
I am getting this [Description] from a link to the SP

So in this case I am only dealing with TSQL and would set this correct only in TSQL

So it is the same column name in the table.


So ok the only thing I need to mention in Access is: "alias_name_goes_here"

Ok thanks....
0
Prakash SamariyaIT ProfessionalCommented:
You could use below code to get your result!!

Left([Despcription], Instr("Membership Renewal")+LEN("Membership Renewal")-1)

Open in new window

0
Amour22015Author Commented:
It looks like:
ID: 40908205

is what I am looking for, I am now just testing and will close soon.

But it also looks like:
ID: 40908346

Would work as well.

Thanks for helping....
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
My solution was T-SQL, the Instr solution is Access expression language, so take your pick..
0
Amour22015Author Commented:
Great Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.