Split string Dynamically

Related searches

I have table as below:

Column1   Column2        Column3
-----------------------------------
A         NULL           NULL
A]B       NULL           NULL
A]B]C     NULL           NULL

There can be millions of records like these and I need a SQL query to obtain output as below

Column1  Column2    Column3
-----------------------------
A        NULL       NULL
A        B          NULL
A        B          C

You can try below query:

    --test data
    declare @tbl table (Col1 varchar(10), Col2 varchar(10), Col3 varchar(10));
    insert into @tbl values
    ('a',null,null),
    ('a]b',null,null),
    ('a]b]c',null,null);


    select case when firstIdx = 0 then col1 else substring(col1, 1, firstIdx - 1) end Col1,
           case when firstIdx = 0 
             then null 
             else case when secondIdx = 0 
               then substring(col1, firstIdx + 1, 100)
               else substring(col1, firstIdx + 1, secondIdx - firstIdx - 1) 
             end
           end Col2,
           case when secondIdx = 0 
             then null
             else substring(col1, secondIdx + 1, 100) 
           end Col3
    from (
        select Col1,
              charindex(']', Col1) firstIdx,
              charindex(']', Col1, charindex(']', Col1) + 1) secondIdx
        from @tbl
    ) a

Split String dynamic, You need to Declare and defined the array first e.g. outside the loop. than you have to replace all the spaces than you can split by | . Sample� Vertica Quick Tip: Dynamically Split Up a String. Posted February 1, 2018 by Soniya Shah, I can use the ROW NUMBER analytic function to dynamically split my text up!

2 step process it is a)you need to create a function as:

CREATE FUNCTION [dbo].[split]
(
    @string varchar(MAX),
    @delimiter CHAR(1),
    @pos INT 
)
RETURNS varchar(255)
AS
BEGIN
    DECLARE @start INT, @end INT, @count INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string), @count = 1 
    WHILE @start < LEN(@string) + 1 BEGIN
        IF @end = 0 
            SET @end = LEN(@string) + 1 

        IF @count = @pos
            RETURN SUBSTRING(@string, @start, @end - @start)

        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)
        SET @count = @count + 1 

    END 
    RETURN '' -- not found
END

b)getting the value of all the for all 3 columns like this

select isnull(dbo.split(cloumn1, ',', 0),'') as Column1,isnull(dbo.split(cloumn1, ',', 1),'') as Column2, isnull(dbo.split(cloumn1, ',', 2),'') as Column3 from <Table_Name>

Split String dynamically - Build, I would to split the after text from 2018 from the string. Right now, I am using delimiters and hardcoding it. E.g. S|social|Service|(Hhh,|1|vCPU|� In summary, the PARSENAME function is a handy addition to your T-SQL toolkit for writing queries involving delimited data. It allows for parsing out and returning individual segments of a string value into separate columns. Since the PARSENAME function breaks down the string, you are not obligated to return all the delimited values.

You can handle this using multiple comma separated CTE.

Below query has two CTE table t0 and t1. Table t0 pulls the position of the first ']' using charindex function and similarly, t1 is used to pull the position of the next ']'.

Using the case statement and substring function you can get the desired output.

with t0 as ( select Column1, Column2 ,  Column3, charindex(']',Column1) pos from #tbl),
     t1 as (select  Column1, Column2 ,  Column3, charindex(']',Column1,t0.pos+1) pos from t0)
select case when t0.pos = 0 then t0.column1 else substring(t0.Column1,0, t0.pos )  end Column1,
       case when t0.pos = 0 then null else substring(t0.Column1,t0.pos+1,case when t1.pos= 0 then len(t0.Column1)+1 else len(t0.Column1)- t1.pos end)end Column2,
       case when (t0.pos = 0 or(t0.pos <>0 and t1.pos =0)) then null else substring(t0.Column1,t1.pos+1, len(t0.Column1)+1 )   end Column3 
from t0
inner join t1 on t0.Column1 = t1.Column1  

output

Column1 Column2 Column3
------- ------- -------
A       NULL    NULL
A       B       NULL
A       B       C

Vertica Quick Tip: Dynamically Split Up a String, As the following example shows, I can use the ROW NUMBER analytic function to dynamically split my text up! dbadmin=> SELECT * FROM� Splitting a string by some delimiter is a very common task. For example, we have a comma separated list of items from a file and we want individual items in an array. Almost all programming languages, provide function split a string by some delimiter.

Dynamically create variables from split string, Dynamically create variables from split string. dynamic variable name matlab vb. net dynamic array dynamic variable names python dynamic array in vb Explanation To split a text string at a certain character, you can use a combination of the LEFT, RIGHT, LEN, and FIND functions. In the example shown, the formula in C5 is: = LEFT(B5,FIND("_", B5) - 1)

Dynamically create variables from splitting string c#, c# split string into characters how to split string in c# and store it in array c# split string by comma c# split string by space string split c# c# dynamic array of� The split() method is used to split a string into an array of substrings, and returns the new array. Tip: If an empty string ("") is used as the separator, the string is split between each character. Note: The split() method does not change the original string.

Split (String [], Int32, StringSplitOptions) Splits a string into a maximum number of substrings based on the strings in an array. You can specify whether the substrings include empty array elements. Split (Char [], Int32, StringSplitOptions) Splits a string into a maximum number of substrings based on the characters in an array.

Comments
  • Do not over-trivialize your sample data. A table has a fixed number of columns and there must be a fixed upper limit in any solution. Have you done your analysis and identified this limit? Don't assume. Splitting strings is a common topic and it will serve you well to learn from past discussion.