script help!!!

• THE ONE

  Valued Member

  Points: 66

  More actions

  October 18, 2005 at 9:52 am

  #89487

  Hi guys,

  Need your help

  I a database column called FILE_EXT and holds file data eg intra_005561.pdf

  What i would like to do in a script is strip the contents of this column and place into two additonal columns.  So in one field would be the file name intra_005561 and the other field would be the file extension eg .pdf

  I have no idea how best to do this so any of you that have done this feel free to post a response.

   

  Cheeers

  Les

• Vasc

  SSCarpal Tunnel

  Points: 4643

  More actions

  October 18, 2005 at 10:51 am

  #598001

  DECLARE @T TABLE (FILE_EXT varchar(50))

  INSERT INTO @T SELECT 'intra_005561.xxx.pdf'

  INSERT INTO @T SELECT 'intra_005561xxxpdf'

  SELECT

  CASE WHEN CHARINDEX('.',REVERSE(FILE_EXT))>0 THEN

  REVERSE(SUBSTRING(REVERSE(FILE_EXT),1,CHARINDEX('.',REVERSE(FILE_EXT))-1))

  ELSE ''

  END as Ext,

  CASE WHEN CHARINDEX('.',REVERSE(FILE_EXT))>0 THEN

  REVERSE(SUBSTRING(REVERSE(FILE_EXT),CHARINDEX('.',REVERSE(FILE_EXT))+1,LEN(FILE_EXT)))

  ELSE FILE_EXT

  END as FileName

  FROM @T

  SELECT REVERSE(SUBSTRING(REVERSE(FILE_EXT),1,CHARINDEX('.',REVERSE(FILE_EXT)))) as Ext

  ,REVERSE(SUBSTRING(REVERSE(FILE_EXT),CHARINDEX('.',REVERSE(FILE_EXT))+1,LEN(FILE_EXT))) as FileName

  FROM @T

  ---

  Kindest Regards,

  Vasc

• Papillon

  Hall of Fame

  Points: 3303

  More actions

  October 20, 2005 at 1:55 am

  #598585

  Hi!!!!!

  SELECT SUBSTRING('intra_005561.pdf',0,PATINDEX('%.p%','intra_005561.pdf')) AS FIRSTPART,RIGHT('intra_005561.pdf',LEN('intra_005561.pdf')+ 1 - PATINDEX('%.p%','intra_005561.pdf')) AS SECONDPART

  ---

  Regards,

  Papillon