Post reply

Truncating String after first space

  • February 15, 2007 at 7:15 am

    #115695

    Hello All,

    Is there a way to truncate strings after the first space?  My table looks like this on the left and I want the results to look like the one on the right. I will be putting this into a #temp table because as I don't have rights to alter in the actual table.  Please advise.

     

    Full Drug NameDrug
    3ML LL SYRNG MIS 25GX13ML 
    ACCU-CHEK    TES DRUMACCU-CHEK 
    ACCUZYME     OINACCUZYME 
    ACE ACD/ALUM SOL 2% OTICACE 
    ACETAZOLAMID TAB 250MGACETAZOLAMID 

     

     

    Thanks in advance,

    Shine

  • February 15, 2007 at 7:37 am

    #689939

    This should do the trick.

     

    SELECT CASE WHEN CharIndex(Full_Drug_Name,'') > 0 THEN Left(Full_Drug_Name,CharIndex(Full_Drug_Name,' ') -1) ELSE Full_Drug_Name END

     

  • February 15, 2007 at 8:31 am

    #689976

    David,

    Thanks for that query, it has me in pointed in the right direction but I ran the query and it seems like I am missing something.  It does'nt seem like its truncating.  Here is what I am doing:

     

    SELECT drug_name,

    CASE

         WHEN  CharIndex(Drug_Name,'') > 0

         THEN LEFT(Drug_Name,CharIndex(Drug_Name,' ') -1)

         ELSE Drug_Name END as Drug

    from dbo.tblPharmacy

    Thanks,

    Shine

     

  • February 15, 2007 at 9:03 am

    #689994

    My bad, I used an empty string instead of the space in the first part. This should do it.

    SELECT CASE WHEN CharIndex(Full_Drug_Name,' ') > 0 THEN Left(Full_Drug_Name,CharIndex(Full_Drug_Name,' ') -1) ELSE Full_Drug_Name END

  • February 15, 2007 at 10:00 am

    #690017

    David,

    Thanks for pointing me in the right direction.

    I got it to work with the following:

     

    select

    Case

    when CHARINDEX(' ', Drug_name) > 0

    then LEFT(Drug_name,CHARINDEX(' ', Drug_Name)-1) else Drug_Name

    end as Drug_Name

    from dbo.tblPharmacy

  • February 15, 2007 at 11:56 am

    #690040

    Glad we could be of help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply