none
Hi i wanted to put each alphabet of a sentence in each row.

    General discussion

  • Hi ,

    For example if the sentence is  "I am Good"

    Identity    alphabet

    1 i

    2

    3 a

    4 m

    5

    6 g

    7 o

    8 o

    9 d

    Thursday, July 24, 2014 12:51 AM

All replies

  • You can use a user defined function that returns a table or you can insert to an existing table. The code you can use would be something like that:

     CREATE TABLE #Alphabets (Id INT IDENTITY, Alphabet NCHAR(1))
    
     DECLARE @Sentence NVARCHAR(MAX) = N'I am Good'
     DECLARE @CurPos AS INT = 0
     IF LEN(ISNULL(@Sentence,'')) <> 0 BEGIN
        WHILE @CurPos < LEN(@Sentence) BEGIN
             SET @CurPos = @CurPos + 1
            INSERT INTO #Alphabets (Alphabet) VALUES (SUBSTRING(@Sentence, @CurPos, 1))
        END
     END
    
    SELECT * FROM #Alphabets
    DROP TABLE #Alphabets
    The result is like this:

    Id    Alphabet
    1    I
    2    
    3    a
    4    m
    5    
    6    G
    7    o
    8    o
    9    d

    Saturday, August 30, 2014 6:10 AM
  • You can use this

    DECLARE @Sentence varchar(1000)= 'I am Good'
    
    ;WIth N1
    AS
    (
    SELECT 1 N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
    ),
    N2
    AS
    (
    SELECT 1 N FROM N1 t1 CROSS JOIN N1 t2
    ),
    N3
    AS
    (
    SELECT 1 N FROM N2 t1 CROSS JOIN N2 t2
    ),
    N4
    AS
    (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Rn
    FROM N3
    )
    
    SELECT SUBSTRING(@Sentence,Rn,1)
    FROM N4
    WHERE Rn <= LEN(@Sentence)


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, August 30, 2014 7:18 PM