J'ai ce genre de cordes,

1. "Drs. H. Ahmad Malik S.Kom S.E S.pd"
2. "H. Ahmad Abdul Malik S.Kom S.E S.pd"

Production attendue :

1. Ahmad Malik
2. Ahmad Abdul Malik

Quelqu'un peut-il m'aider à le résoudre, j'ai essayé avec substring, patindex mais je n'ai toujours pas obtenu le résultat attendu

2
Muhammad Idrus 3 avril 2017 à 06:38

2 réponses

Meilleure réponse

Mis à jour pour de nouvelles informations

Declare @YourTable table (ID int,Name varchar(100))
Insert Into @YourTable values
(4,'Drs. H. Ahmad Malik A. N. S.Kom S.E S.pd'),
(1,'Drs. H. Ahmad Malik S.Kom S.E S.pd'),
(2,'H. Ahmad Abdul Malik S.Kom S.E S.pd'),
(3,'Drs. H. Ahmad Malik S.Kom S.E S.pd')

Select A.*
      ,C.*
 From  @YourTable A
 Cross Apply (Select * From [dbo].[udf-Str-Parse](A.Name,'S.Kom') Where RetSeq=1) B
 Cross Apply (
              Select NewString = Stuff((Select  ' ' +RetVal 
                                        From (
                                              Select *,Excl=min(Flag) over (Order By RetSeq)
                                               From (Select *,Flag=case when charindex('.',RetVal)>0 then 1 else 0 end From [dbo].[udf-Str-Parse](B.RetVal,' ') ) C1
                                             ) C2
                                        Where Excl=0
                                        Order by RetSeq
                                        For XML Path ('')),1,1,'')
             ) C

Retours

ID  Name                                        NewString
1   Drs. H. Ahmad Malik S.Kom S.E S.pd          Ahmad Malik
2   H. Ahmad Abdul Malik S.Kom S.E S.pd         Ahmad Abdul Malik
3   Drs. H. Ahmad Malik S.Kom S.E S.pd          Ahmad Malik
4   Drs. H. Ahmad Malik A. N. S.Kom S.E S.pd    Ahmad Malik A. N.   << Notice A. N.
0
John Cappelletti 3 avril 2017 à 05:33

À peu près n'importe quelle fonction d'analyse / fractionnement ferait l'affaire.

Option 1 avec une fonction d'analyse / fractionnement

Declare @YourTable table (ID int,Name varchar(100))
Insert Into @YourTable values
(1,'Drs. H. Ahmad Malik S.Kom S.E S.pd'),
(2,'H. Ahmad Abdul Malik S.Kom S.E S.pd')

Select A.*
      ,B.*
 From  @YourTable A
 Cross Apply (
              Select NewString = Stuff((Select  ' ' +RetVal 
                From [dbo].[udf-Str-Parse](A.Name,' ') 
                Where RetVal Not Like '%.%'
             Order by RetSeq
             For XML Path ('')),1,1,'')
  ) B

Option 2 sans fonction d'analyse / fractionnement

Select A.*
      ,B.*
 From  @YourTable A
 Cross Apply (
              Select NewString = Stuff((Select  ' ' +RetVal 
                From (
                        Select RetSeq = Row_Number() over (Order By (Select null))
                              ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                        From  (Select x = Cast('<x>' + replace((Select replace(A.Name,' ','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                        Cross Apply x.nodes('x') AS B(i)
                     ) B1
                Where RetVal Not Like '%.%'
             Order by RetSeq
             For XML Path ('')),1,1,'')
  ) B

Les deux reviennent

ID  Name                                  NewString
1   Drs. H. Ahmad Malik S.Kom S.E S.pd    Ahmad Malik 
2   H. Ahmad Abdul Malik S.Kom S.E S.pd   Ahmad Abdul Malik 

L'UDF si vous êtes intéressé

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(25))
Returns Table 
As
Return (  
    with   cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
           cte2(N)   As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a,cte1 b,cte1 c,cte1 d) A ),
           cte3(N)   As (Select 1 Union All Select t.N+DataLength(@Delimiter) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter)) = @Delimiter),
           cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter,@String,s.N),0)-S.N,8000) From cte3 S)

    Select RetSeq = Row_Number() over (Order By A.N)
          ,RetVal = LTrim(RTrim(Substring(@String, A.N, A.L)))
    From   cte4 A
);
--Orginal Source http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Much faster than str-Parse, but limited to 8K
--Select * from [dbo].[udf-Str-Parse-8K]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse-8K]('John||Cappelletti||was||here','||')
0
John Cappelletti 3 avril 2017 à 04:13