none
how to split 3 charters upto last character in sql server

    Question

  • Hi I have one doubt in sql server 

    how to split length of 3 upto last charcters in sql server 

    table : emp 
    id  | name  
    1   | harikrishnaxx
    2   | Baludevu

    based on above data I want output like below : 

    id  | name 
    1   | har
    1   |ikr
    1   |ish
    1   |nax
    1   |x
    2   | bal
    2   |ude
    2   |vu


    i tried like below : 
    1st I create tablevalued funtion then called this function in emp table 

    create  FUNCTION DatesBetween(@name varchar(50))
    RETURNS @dates TABLE (
       DateValue varchar(50)  NULL

    AS
    BEGIN
    declare @fin varchar(50)
    declare @len int = (select len(@name))
    declare @i int 
    set @i=1
    while (@i <=@len)
    begin
    insert into @dates (DateValue)values(@fin)
    set  @fin =SUBSTRING (@name ,@i,3)
    set @i=@i+3
       END;
     RETURN;
    END;

    select  id ,   dbo.DatesBetween(name ) from emp 

    but above query not getting exact result .
    can you please tell me how to solve this issue in sql server 
    Saturday, April 13, 2019 5:15 PM

All replies

  • Review the logic of your function a little closer. You will find that you need to swap two lines.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, April 13, 2019 7:55 PM
  • Review the logic of your function a little closer. You will find that you need to swap two lines.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, April 13, 2019 7:56 PM
  • insert into @dates (DateValue)values(@fin)
    set  @fin =SUBSTRING (@name ,@i,3)

    The catch is that you are inserting before setting the value. Swapping will resolve the issue!

    Sunday, April 14, 2019 5:26 AM
  • Hi I have one doubt in sql server 

    how to split length of 3 upto last charcters in sql server 

    table : emp 
    id  | name  
    1   | harikrishnaxx
    2   | Baludevu

    based on above data I want output like below : 

    id  | name 
    1   | har
    1   |ikr
    1   |ish
    1   |nax
    1   |x
    2   | bal
    2   |ude
    2   |vu


    i tried like below : 
    1st I create tablevalued funtion then called this function in emp table 

    create  FUNCTION DatesBetween(@name varchar(50))
    RETURNS @dates TABLE (
       DateValue varchar(50)  NULL

    AS
    BEGIN
    declare @fin varchar(50)
    declare @len int = (select len(@name))
    declare @i int 
    set @i=1
    while (@i <=@len)
    begin
    insert into @dates (DateValue)values(@fin)
    set  @fin =SUBSTRING (@name ,@i,3)
    set @i=@i+3
       END;
     RETURN;
    END;

    select  id ,   dbo.DatesBetween(name ) from emp 

    but above query not getting exact result .
    can you please tell me how to solve this issue in sql server 

    you really dont need a loop

    you can use a number table based approach also to get the required result

    like

    create  FUNCTION DatesBetween(@name varchar(50))
    RETURNS @dates TABLE (
    ID int IDENTITY(1,1) NOT NULL, DateValue varchar(50) NULL ) AS BEGIN ;With Numbers(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ),NumberTable AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Seq FROM NUmbers n1 CROSS JOIN NUmbers n2 CROSS JOIN NUmbers n3 CROSS JOIN NUmbers n4 CROSS JOIN NUmbers n5 CROSS JOIN NUmbers n6 ) insert into @dates (DateValue) SELECT SUBSTRING(@name,Seq,3) FROM NumberTable WHERE Seq <= LEN(@name) AND Seq % 3 = 1 RETURN; END;

    and it needs to be invoked like this as its a table valued udf

    select  e.id ,  f.DateValue AS name
    from emp  e
    cross apply  dbo.DatesBetween(e.name ) f
    ORDER BY e.Id,f.ID



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, April 14, 2019 6:11 AM
  • SELECT P.ID,
           P.Name,
           SUBSTRING(P.Name, B.StartLocation, 3) AS PartName
    FROM
    (
        SELECT 1 AS ID,
               'ABCDEFGHIJKLMN' AS Name
        UNION ALL
        SELECT 2,
               '123456789SNVF'
    ) P
        INNER JOIN
        (
            SELECT TOP 20000
                   ROW_NUMBER() OVER (ORDER BY C.name) * 3 - 2 AS StartLocation
            FROM sys.columns C
        ) B
            ON LEN(P.Name) >= B.StartLocation
    ORDER BY 1,
             B.StartLocation
    Monday, April 15, 2019 3:49 AM
  • Hi balakrishna324,

    You could try to use below query to see whether it works or not

    CREATE  function SplitString
    (   
        @str varchar(max),
        @length int
    )
    RETURNS @Results TABLE( Result varchar(50),Sequence INT ) 
    AS
    BEGIN
    
    DECLARE @Sequence INT 
    SET @Sequence = 1
    
        DECLARE @s varchar(50)
        WHILE len(@str) > 0
        BEGIN
            SET @s = left(@str, @length)
            INSERT @Results VALUES (@s,@Sequence)
    
            IF(len(@str)<@length)
            BREAK
    
            SET @str = right(@str, len(@str) - @length)
            SET @Sequence = @Sequence + 1
        END
        RETURN 
    END
    
    
    create table w( id int, name varchar(50))
    insert into w values (1,'asdfafsaddafd'), (2,'ss2ddwfesd')
    
    
    
    SELECT s.id,f.result 
      FROM w AS s
      CROSS APPLY dbo.SplitString(s.name, 3) as f;
    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 15, 2019 5:36 AM
  • Hi 

    I minimally change your function as forwarding

    create  FUNCTION DatesBetween(@name varchar(50))
    RETURNS @dates TABLE (
       DateValue varchar(50)  NULL
    ) 
    AS
    BEGIN
    declare @fin varchar(50)
    declare @len int = (select len(@name))
    declare @i int 
    set @i=1
    while (@i <=@len+3)
    begin
    if @fin is not null
    insert into @dates (DateValue)values(@fin)
    set  @fin =SUBSTRING (@name ,@i,3)
    set @i=@i+3
       END;
     RETURN;
    END;

    Hope it can help you.

     

    Best Regards,

    Natig


    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. 


    Monday, April 15, 2019 6:43 AM
  • create table emp  (id int,name   varchar(30) )
    
    insert into emp  (id,name ) values
    (1,'harikrishnaxx')
    ,(2,'Baludevu')
     
    
    ;with  Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
    
    
    ,mycte as (
    select id,name ,mySplit, n,(n-1)/3 grp  
    from Nums Cross apply (Select SUBSTRING(name  ,n,1 ), name ,id  from emp ) as d(mySplit,name,id)
    where len(name)>=n
    
    )
    
     
     SELECT t1.id, 
            ( SELECT  mySplit 
               FROM mycte t2
              WHERE t2.id = t1.id and  t2.grp = t1.grp
              ORDER BY id,n
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)')    AS names
      FROM mycte t1
     GROUP BY t1.id,t1.grp;
     
     
    
    drop table emp 
    

    Monday, April 15, 2019 3:28 PM
    Moderator