Stack Exchange
How to generate new rows depending on sting value from one column in sql?
I need to generate new rows depending on value from "Number" column. I've tried CHARINDEX and STUFF function without any success.
Exampledata (where ".." means in between, "| means and):
CREATE TABLE mytable(
   Row         VARCHAR(3) NOT NULL PRIMARY KEY
  ,Description VARCHAR(11)
  ,Number      VARCHAR(26)
);
INSERT INTO mytable(Row,Description,Number) VALUES ('100','Testing','1105..1110|2805|2820..2830');
INSERT INTO mytable(Row,Description,Number) VALUES (NULL,NULL,NULL);
INSERT INTO mytable(Row,Description,Number) VALUES (NULL,NULL,NULL);

Expected output:
+-----+-------------+--------+
| Row | Description | Number |
+-----+-------------+--------+
| 100 | Testing     |   1105 |
| 100 | Testing     |   1106 |
| 100 | Testing     |   1107 |
| 100 | Testing     |   1108 |
| 100 | Testing     |   1109 |
| 100 | Testing     |   1110 |
| 100 | Testing     |   2805 |
| 100 | Testing     |   2820 |
| 100 | Testing     |   2821 |
| 100 | Testing     |   2822 |
| 100 | Testing     |   2823 |
| 100 | Testing     |   2824 |
| 100 | Testing     |   2825 |
| 100 | Testing     |   2826 |
| 100 | Testing     |   2827 |
| 100 | Testing     |   2828 |
| 100 | Testing     |   2829 |
| 100 | Testing     |   2830 |
+-----+-------------+--------+


"I've tried CHARINDEX and STUFF function without any success." What did you try, specifically?

To get where ".." and "|" are in the string i used Charindex as below:
CHARINDEX('..', [Number], 2) AS MatchPosition,
CHARINDEX('|', [Number], 1) AS MatchPosition
And then i used STUFF to replace, but I cant manage to replace into new rows to get correct values.

Probably unrelated, but: the second and third INSERT statement of your sample data will not work as you can't store NULL in the primary key column.

The data does not contain Null, the empty rows was generated by Excel, sorry for the inconvenience.

@vasdan, could you please check following SQL script
Please note that I used to user-defined function which you can find similar ones on the web
First one is SQL split function
The second is SQL numbers function
;with cte as (
    select 
        t.row,
        t.description,
        t.number,
        s.id grpno,
        replace(s.val,'..','|') as val
    from mytable as t
    cross apply dbo.split(t.Number,'|') s
), cte2 as (
select distinct
    row,
    description,
    grpno,
    case when ( count(*) over (partition by row, grpno) ) = 1 then null else 'X' end as range,
    min(s.val) over (partition by row, grpno) minval,
    max(s.val) over (partition by row, grpno) maxval
from cte
cross apply dbo.split(cte.val,'|') s
)
select
row,description,i
from cte2
cross apply dbo.NumbersTable(minval,maxval,1) n

Here is the output

If you need help, I'ld like to help you further on this example

Thank you @Eralper, this post helped me to solve the challenge.

Use a recursive CTE to bulk create the numbers, use a parameter for the big string, insert only those that appear in the string
with CTE as
(
select 1105 as NN
union all
select NN + 1
from CTE
where NN < 2830
)
insert into MyTable
select 100, 'Testing', NN
from CTE
where @MyString like '%'+NN+'%'


The easiest way is to create a Numbers table for yourself.  Having a numbers table is useful in numerous situations just like this one.  It allows you to do very easy SQL queries on ranges of numbers.
Check out this MSDN article: The SQL Server Numbers Table, Explained
Once you have a numbers table, you can easily populate your target table with values "WHERE numbers.number between 1105 and 1110"
SQL 2005 introduced the Common Table Expression (CTE) and the ROW_NUMBER() window function enabling in-line creation of numbers tables.
https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx
Table Value Constructor's (TVC) were introduced in SQL 2008 which offers a very compact way to create a numbers table tailored specifically to the precise number of rows you need. 
https://technet.microsoft.com/en-us/library/dd776382(v=sql.110).aspx
-- Further reading: http://dataeducation.com/you-require-a-numbers-table/

not the one that downvoted, but a suggestion if anyone's going to go this route, it's probably best to stay away from the between operator, as it's not consistent with different database implementations.  A better way is to use >= and <= respectively.  This makes it a little more clear what your intent is too, as some db's use the between operator as inclusive, and some don't.

The between operator is consistent across all DBMS implementations (that I know of). Which database does not implement that correctly?
