Find the second occurrence in the string

• sks_989

  SSC Eights!

  Points: 843

  More actions

  August 2, 2018 at 5:56 pm

  #355375

  I have standard data as

  C1588-27-3
  C1588-28-3
  C1588-29-8

  I want result as  in SQL as

  1588-27
  1588-28
  1588-29

  I tried replacefirst character REPLACE('C1588-27-3','C','') it works then i want to usesubstring so try to find CHARINDEX
  it always give me 0 so try with  PATINDEX that works but it give mefirst occurrence i want second occurrence is any idea how this will work. Appreciateyour help.

  CHARINDEX giving 0 instead of 6 characters
  SELECT PATINDEX('%-%', 'C1588-27-3'), CHARINDEX('C1588-27-3','-')

• andycadley

  SSCertifiable

  Points: 5296

  More actions

  August 2, 2018 at 7:42 pm

  #2000104

  Drop Table If Exists #Temp
  Create Table #Temp
  (
   Code VarChar(20)
  )

  Insert Into #Temp(Code)
  Values
  ('C1588-27-3'),
  ('C1588-28-3'),
  ('C1588-29-8')

  Select Code, SubString(Code, 2, SecondHypen - 2)
  From #Temp
  Cross Apply (Select CharIndex('-', Code, CharIndex('-', Code) + 1) As SecondHypen) S

• sks_989

  SSC Eights!

  Points: 843

  More actions

  August 2, 2018 at 8:42 pm

  #2000110

  Thanks it worked like charm!!!

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  August 2, 2018 at 10:03 pm

  #2000111

  sks_989 - Thursday, August 2, 2018 5:56 PM

  I have standard data as

  C1588-27-3
  C1588-28-3
  C1588-29-8

  I want result as  in SQL as

  1588-27
  1588-28
  1588-29

  I tried replacefirst character REPLACE('C1588-27-3','C','') it works then i want to usesubstring so try to find CHARINDEX
  it always give me 0 so try with  PATINDEX that works but it give mefirst occurrence i want second occurrence is any idea how this will work. Appreciateyour help.='font-variant-ligatures:>

  CHARINDEX giving 0 instead of 6 characters
  SELECT PATINDEX('%-%', 'C1588-27-3'), CHARINDEX('C1588-27-3','-')

  Is the format and segment lengths of the data always the same?
  

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  August 3, 2018 at 5:31 am

  #2000141

  andycadley - Thursday, August 2, 2018 7:42 PM

  Drop Table If Exists #Temp
  Create Table #Temp
  (
   Code VarChar(20)
  )

  Insert Into #Temp(Code)
  Values
  ('C1588-27-3'),
  ('C1588-28-3'),
  ('C1588-29-8')

  Select Code, SubString(Code, 2, SecondHypen - 2)
  From #Temp
  Cross Apply (Select CharIndex('-', Code, CharIndex('-', Code) + 1) As SecondHypen) S

  This will  work only if you 2nd occurrence of string 
  
Drop Table If Exists #Temp
Create Table #Temp
(
Code VarChar(20)
)

  Insert Into #Temp(Code)
Values
('C1588-27-3'),
('C1588-28-3'),
('C1588-29-8');
Solution:
select substring(Code,1,(charindex('-',code)+charindex('-',reverse(code))))
from #Temp;


  Saravanan

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  August 3, 2018 at 9:32 am

  #2000186

  
Select Code, new_string
FROM #Temp
CROSS APPLY (
    SELECT STUFF(Code, 1, 1, '') AS base_string
) AS ca1
CROSS APPLY (
    SELECT LEFT(base_string, CHARINDEX('-', base_string,
        CHARINDEX('-', base_string) + 1) - 1) AS new_string
) AS ca2

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".