SQL server varchar.toArray() or string.toArray() method

Question

I want to convert my string data to array in sql server. I tried it like below.

SELECT '223456789' AS SerialOriginalCode
-- SerialOriginalCode 223456789
DECLARE @tbl_SerialOriginalVerion TABLE(ID INT, SerialOriginalCode INT);
INSERT INTO @tbl_SerialOriginalVerion VALUES
(1, 2),
(2, 2),
(3, 3),
(4, 4),
(5, 5),
(6, 6),
(7, 7),
(8, 8),
(9, 9);
SELECT * FROM @tbl_SerialOriginalVerion

But it is manual way, it is not the way of programmatic convertion as it needs me to key in every insert value for each line.

Could someone please suggest me more elegent way?

i would recommend you do this on the application side. databases are not optimized for such recursive procedural tasks. — ughai
– ughai, Commented Apr 20, 2015 at 6:48
I dont think SQL Server supports any of those two method: varchar.toArray() or string.toArray() method — Rahul Tripathi
– Rahul Tripathi, Commented Apr 20, 2015 at 6:50

Munavvar Husein Shaikh · Accepted Answer · 2015-04-20 06:56:47Z

3

DECLARE @InputText AS VARCHAR(MAX) = '223456789'
DECLARE @Pos Int = 1
DECLARE @End Int
DECLARE @TextLength Int = DATALENGTH(@InputText)

DECLARE @Array TABLE 
(
  TokenID Int PRIMARY KEY IDENTITY(1,1),
  Match Varchar(MAX)
)

-- Exit function if no text is passed in.
IF @TextLength <> 0 
  BEGIN
    WHILE @Pos <= @TextLength BEGIN
      INSERT @Array (Match) VALUES (SUBSTRING(@InputText,@Pos,1))
      SET @Pos = @Pos + 1
    END
  END

SELECT * FROM @Array

answered Apr 20, 2015 at 6:56

Munavvar Husein Shaikh

5837 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sqluser · Accepted Answer · 2015-04-20 07:13:16Z

1

Try this INSERT using number from master..spt_values

SELECT '223456789' AS SerialOriginalCode
-- SerialOriginalCode 223456789
DECLARE @tbl_SerialOriginalVerion TABLE(ID INT, SerialOriginalCode INT);

INSERT INTO @tbl_SerialOriginalVerion
SELECT number + 1, SUBSTRING(t.SerialOriginalCode, sv.number + 1, 1) 
FROM (SELECT '223456789' AS SerialOriginalCode) t
INNER JOIN master..spt_values sv ON sv.number < LEN(t.SerialOriginalCode)
WHERE sv.[type] = 'P'


SELECT * FROM @tbl_SerialOriginalVerion

Output:

ID  SerialOriginalCode
1   2
2   2
3   3
4   4
5   5
6   6
7   7
8   8
9   9

edited Apr 20, 2015 at 7:13

answered Apr 20, 2015 at 6:55

sqluser

5,7127 gold badges44 silver badges52 bronze badges

Comments

ughai · Accepted Answer · 2015-04-20 07:31:13Z

1

I would recommend that you do this in your application. SQL is optimised for Set based operations and does not handle recursive procedural tasks like like these.

If you want to do this in SQL, you can find the LEN of your string and then recursively find the next character. Something like this

Query

DECLARE @SerialOriginalCode VARCHAR(20) = '223456789'

DECLARE @tbl_SerialOriginalVerion TABLE(ID INT, SerialOriginalCode INT);

DECLARE @len INT = LEN(@SerialOriginalCode)
;WITH CTE as 
(
SELECT 1 as ID,CONVERT(INT,SUBSTRING(@SerialOriginalCode,1,1)) as CharInt
UNION ALL 
SELECT ID + 1,CONVERT(INT,SUBSTRING(@SerialOriginalCode,ID + 1,1))
FROM CTE WHERE LEN(@SerialOriginalCode) >= ID + 1
)
INSERT INTO @tbl_SerialOriginalVerion(ID,SerialOriginalCode)
SELECT * FROM CTE;

SELECT * FROM @tbl_SerialOriginalVerion

Output

ID  SerialOriginalCode
1   2
2   2
3   3
4   4
5   5
6   6
7   7
8   8
9   9

edited Apr 20, 2015 at 7:31

answered Apr 20, 2015 at 6:56

ughai

9,9004 gold badges33 silver badges48 bronze badges

3 Comments

Frank Myat Thu Over a year ago

Thank for your help @ughai. It will be more helpful, if you can submit your customized function "TRY_CONVERT"

ughai Over a year ago

Use CONVERT instead of TRY_CONVERT if you are using any version before SQL Server 2014

Frank Myat Thu Over a year ago

Thank you @ughai for your continuous help

Giorgi Nakeuri · Accepted Answer · 2015-06-09 04:46:36Z

1

With cte and Apply:

Declare @s nvarchar(9) = '223456789'

;with cte as(
select n from (values(1),(2),(3),(4),(5),(6),(7),(8),(9)) as t(n))
select * from cte
cross apply(select substring(@s, cte.n, 1) as c) ca

Output:

Or you can do it with tally tables:

declare @s varchar(100) = '223456789'

;with t as(select row_number() over(order by (select null)) rn from
          (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t1(n) cross join
          (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t2(n))
select substring(@s, rn, 1) from t
where rn <= len(@s)

Fiddle http://sqlfiddle.com/#!3/9eecb7db59d16c80417c72d1/570

edited Jun 9, 2015 at 4:46

answered Apr 20, 2015 at 6:58

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

2 Comments

ughai Over a year ago

it will only for work for string with exact 9 characters

Frank Myat Thu Over a year ago

those exact 9 characters are enough for my cause @ughai.

Collectives™ on Stack Overflow

SQL server varchar.toArray() or string.toArray() method

4 Answers 4

Comments

Comments

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related