Truncating String after first space

• Shine G

  Mr or Mrs. 500

  Points: 519

  More actions

  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 Name	Drug
  3ML LL SYRNG MIS 25GX1	3ML
  ACCU-CHEK    TES DRUM	ACCU-CHEK
  ACCUZYME     OIN	ACCUZYME
  ACE ACD/ALUM SOL 2% OTIC	ACE
  ACETAZOLAMID TAB 250MG	ACETAZOLAMID

   

   

  Thanks in advance,

  Shine

• David McFarland

  SSChampion

  Points: 11815

  More actions

  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

   

• Shine G

  Mr or Mrs. 500

  Points: 519

  More actions

  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

   

• David McFarland

  SSChampion

  Points: 11815

  More actions

  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

• Shine G

  Mr or Mrs. 500

  Points: 519

  More actions

  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

• David McFarland

  SSChampion

  Points: 11815

  More actions

  February 15, 2007 at 11:56 am

  #690040

  Glad we could be of help.