remove only Leading and trailing double-double-quotes

• RamG

  Right there with Babe

  Points: 767

  More actions

  December 18, 2009 at 12:01 am

  #136131

  Hi,

  I want to remove the Leading and trailing double-double-quotes("") and replaced with a double-quote (")

  Example: ""description"" needs to look like "description"

  And Embedded double-double-quotes ("") and doube-quotes (") should be left alone.

  Example: desc""ription should still end up as desc""ription(same for double-quotes (").

  The following query is workig fine for me:

  declare @des varchar(80)

  select @des = '""desc""ripti""on""'

  select @des = case when left(@des, 2) = '""' then right(@des,len(@des)- 1) else @des end

  select @des = case when right(@des, 2) = '""' then left(@des,len(@des)- 1) else @des end

  print @des

  but the query is not working for the string ''""""desc""ripti""on""''

  declare @des varchar(80)

  select @des = '""""desc""ripti""on""'

  select @des = case when left(@des, 2) = '""' then right(@des,len(@des)- 1) else @des end

  select @des = case when right(@des, 2) = '""' then left(@des,len(@des)- 1) else @des end

  print @des

  Please give me the solution for remove only the leading and trailing double-double qoutes and make it as single double quotes.

• Nabha

  SSCrazy Eights

  Points: 8534

  More actions

  December 18, 2009 at 12:24 am

  #1093889

  Sorry if I've misunderstood ur requirement, but is this what you need?

  (this is what I could think of using STUFF, there could be better ways!)

  declare @des varchar(80)

  select @des = '""desc""ripti""on""'--'""descr""iption""'

  PRINT REVERSE(STUFF(REVERSE(STUFF(@des,1,2,'"')),1,2,'"'))

  PRINT STUFF(STUFF(@des,1,2,'"'),len(@des)-2,2,'"')

  ---------------------------------------------------------------------------------

• RamG

  Right there with Babe

  Points: 767

  More actions

  December 18, 2009 at 12:43 am

  #1093894

  Thanks for ur reply.

  It will work for the below string

  eg: select @des = '""desc""ripti""on""'

  could you please check with the following string

  eg: select @des = '""""desc""ripti""on""'

• arun.sas

  SSChampion

  Points: 11831

  More actions

  December 18, 2009 at 1:02 am

  #1093901

  Hi,

  Try this

  declare @des varchar(80)

  select @des = '""desc""ripti""on""'

  select @des

  --select PATINDEX('%[A-Z]%',@des)

  --select len(@des)-1-PATINDEX('%[A-Z]%',reverse(@des))

  select '"'+substring(@des,PATINDEX('%[A-Z]%',@des),(len(@des)-1-PATINDEX('%[A-Z]%',reverse(@des))))+'"'

• RamG

  Right there with Babe

  Points: 767

  More actions

  December 18, 2009 at 2:52 am

  #1093925

  Hi,

  Thanks for your help. Its working fine.