Print the portion of a string past a special character

Posted on 2012-09-06
Last Modified: 2012-09-06

How would I go about editing this script to update my column with only the portion of the string after a special character such as . or -  ?

update TABLEdata		 
set emailGrouping=case when employeestatus!='Terminated' and emailGrouping is null and DepartmentCode like '2%' 
				then substring(DepartmentDescription, 8, 45) 
		 When employeestatus!='Terminated' and emailGrouping is NOT null and DepartmentCode like '2%'
				then emailGrouping+', '+substring(DepartmentDescription, 8, 45) else emailgrouping

Open in new window

For example, if a string looks like this after the update script is run:


I would like it to show:

Question by:susnewyork
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    Do you have more sample values, in the sample you have mentioned, you are selecting everything before the second '-' thats confusing
    LVL 1

    Author Comment

    That's basically what I need.

    Another example of what would need to be excluded would be this:


    I would need:

    DD-2ndValue from that string.
    LVL 75

    Accepted Solution

    try this way

    declare @str varchar(100) ='DD-2ndValue.LocationDate'

    select left (@Str, (case when CHARINDEX('-',@Str,4) = 0 then CHARINDEX('.',@Str,4)  else CHARINDEX('-',@Str,4) end ) -1 )
    LVL 1

    Author Closing Comment

    Perfect, thanks.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

         When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now