17661

How can I insert each character of a string which is not comma delimited to separate row in a table?

Question:

I have a string like

Declare @Str varchar (15) = '0123456789'

and I have a table

CREATE TABLE TableNum(Nums Varchar(1));

How can I insert each number to a separate row?

My output should be

Nums 0 1 2 3 4 . .

Answer1:

Using a numbers table is probably the best and simplest way to do it. If you don't alrady have a numbers table, you can create one:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number INTO Tally FROM sys.objects s1 CROSS JOIN sys.objects s2 ALTER TABLE Tally ADD CONSTRAINT PK_Tally PRIMARY KEY CLUSTERED (Number)

Then all you have to do is simply this:

INSERT INTO TableNum(Nums) SELECT SUBSTRING(@Str, Number, 1) FROM Tally WHERE Number <= LEN(@Str)

For more information on why you want a numbers (or tally) table, read <a href="http://www.sqlservercentral.com/articles/T-SQL/62867/" rel="nofollow">this article</a> by Jeff Moden.

BTW, using varchar(1) makes no sense - use char(1) instead.

Answer2:

<strong>Try this :</strong> Using <kbd><a href="https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx" rel="nofollow">CTE</a></kbd> you can achieve this, No need to use any joins with existing system tables

;WITH CTE AS( SELECT CAST('' AS NVARCHAR(1)) AS CH, 1 AS CNT UNION ALL SELECT CAST(SUBSTRING(@Str,CNT,1)AS NVARCHAR(1)) AS CH , CNT+1 FROM CTE WHERE CNT<=LEN(@Str) ) INSERT INTO TableNum(Nums) SELECT CH FROM CTE WHERE CH <> ''

Answer3:

If you can't add a tally table (<strong>which the way to go as illustrated by Zohar Peled</strong>), you can use an ad-hoc tallty table

Declare @Str varchar (15) = '0123456789' Select Num=substring(@Str,N,1) From (Select Top (Len(@Str)) N=Row_Number() Over (Order By (Select null)) From master..spt_values ) N

Returns

Num 0 1 2 3 4 5 6 7 8 9

<strong>Another option is to create a TVF</strong>

Declare @Str varchar (15) = '0123456789' Select * from [dbo].[udf-Str-Parse-Char](@Str)

<strong>The UDF</strong>

CREATE FUNCTION [dbo].[udf-Str-Parse-Char] (@String varchar(max)) Returns Table As Return ( with cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)), cte2(N) As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From cte1 a,cte1 b,cte1 c,cte1 d,cte1 e,cte1 f) Select RetSeq=N ,RetVal=Substring(@String,N,1) From cte2 ) --Max 1 Million Observations --Select * from [dbo].[udf-Str-Parse-Char]('this is a string')

Answer4:

Declare @Str varchar (15) = '0123456789' ,@pos INT, @result VARCHAR(100); SET @result = @Str; SET @pos = 2 -- location where we want first space WHILE @pos < LEN(@result)+1 BEGIN SET @result = STUFF(@result, @pos, 0, SPACE(1)); SET @pos = @pos+2; END SELECT @result=REPLACE( @result,' ',',') --Print @result SELECT Split.a.value('.', 'VARCHAR(100)') AS Num FROM ( SELECT CAST ('<M>' + REPLACE(@result, ',', '</M><M>') + '</M>' AS XML) AS Num ) AS A CROSS APPLY Num.nodes ('/M') AS Split(a) <hr />

OutPut

Num 0 1 2 3 4 5 6 7 8 9

Answer5:

Try this

DECLARE @Str varchar(10) = '0123456789' DECLARE @Count INT=1 WHILE @Count <= LEN(@Str) BEGIN INSERT INTO TableNum SELECT substring(@Str, @count, 1) SET @Count = @Count + 1 END

and then

SELECT * FROM TabNum

Answer6:

Try this.

CREATE TABLE TableNum(Nums Varchar(1)); TRUNCATE TABLE TableNum; Declare @Str varchar (15) = '0123456789'; DECLARE @nLIMIT AS INT = LEN(@Str); DECLARE @nCounter AS INT = 1; WHILE @nCounter <= @nLIMIT BEGIN INSERT INTO TableNum VALUES(SUBSTRING (@Str,@nCounter,1)); SET @nCounter = @nCounter + 1; IF @nCounter > @nLIMIT BREAK ELSE CONTINUE END; SELECT * FROM TableNum;

Recommend

  • Tableau - data contains start / end date, how to make a fixed access for every month between?
  • SMTP error code compliance
  • Error when inserting into temporal table using Entity Framework Core 2.1
  • TSQL How to select employee with skills in xml column
  • Finding the mode of an array
  • How can I specify the natural ordering in MongoDB?
  • JsonCpp heap object handling memory management
  • Msg 207 Invalid column name $node_id for pseudo column in inline table valued function
  • Multiple Servers And Memcached
  • Is a collocated join (a-la-netezza) theoretically possible in hive?
  • Share session on subdomains in php
  • Javascript, map returns undefined
  • FolderBrowserDialog crashes the application
  • Sql Server 2005 Xml data type has CDATA removed on INSERT
  • Algorithm to determine thread “hotness”
  • SQL Service Broker: Collecting data from distributed sources.
  • Kill Active Session if User Is Banned
  • Dendrogram or Other Plot from Distance Matrix
  • Expand header row into multiple child rows
  • How to merge two tables and transpose rows to columns
  • Fraction length
  • SQL append distinct values from two columns and two tables
  • HALF_PTR Windows data type
  • JQuery .html() remove line break on IE 8
  • Retrieving specified columns from a list of csv files to create a data data frame in R
  • Produce a precision weighted average among rows with repeated observations
  • Filter strings with regex before casting to numeric
  • C# program and C++ DLL compiled for 32-bit system crash on 64-bit system
  • Remove final comma from string in vb.net
  • copying resource to sdcard gives a damaged file in android
  • Get object from AWS S3 as a stream
  • DirectX11 ClearRenderTargetViewback with transparent buffer?
  • Why is the timeout on a windows udp receive socket always 500ms longer than set by SO_RCVTIMEO?
  • Web-crawler for facebook in python
  • How to get icons for entities from eclipse?
  • InvalidAuthenticityToken between subdomains when logging in with Rails app
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • SQL merge duplicate rows and join values that are different
  • LevelDB C iterator
  • Can't mass-assign protected attributes when import data from csv file