SQL query to spit from string

Question

declare @st1 varchar(50) = 'Today+Test+12456'

I am trying to insert into a temp table as below.

CREATE TABLE #TempTable (
    Id INT IDENTITY,
    col1 varchar(50), 
    col2 varchar(50), 
    col3 nvarchar(50)
);
insert into #TempTable select Today,Test,12456;

eg: select Today,Test,12456

I am not looking to query as rows like below.

Today
Test
123456

And what values are those columns going to have? `@ST1` is scalar variable, you can't use it in a `FROM` clause. — Thom A, Jan 08 '19 at 22:49
I thought that @AngelM., But the op isn't looking for rows. There are similar dupes still, depending on their final requirement. — Thom A, Jan 08 '19 at 22:51
SQL Server is declarative by design. It does not support macro substitution, You would have to replace the + with a comma and execute as dynamic sql — John Cappelletti, Jan 08 '19 at 22:52
@JohnCappelletti i can replace it with a comma. But how do i get it? — RDeveloper, Jan 08 '19 at 22:53
Possible duplicate of [How to split a comma-separated value to columns](https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns). — Thom A, Jan 08 '19 at 22:53
Yes @Larnu, it looks equal but it is not. So, I think the solution comes from Jonh's proposal... — Angel M., Jan 08 '19 at 22:54
Also is similar to your second proposal as duplicate but it is not. On this one it is in a variable and wants to obtain the SELECT clause — Angel M., Jan 08 '19 at 22:56
@Larnu I already looked that link but it didnt have solution for 3 values. Its only for 2 values — RDeveloper, Jan 08 '19 at 22:56
Possible duplicate of [T-SQL split string](https://stackoverflow.com/questions/10914576/t-sql-split-string) — sticky bit, Jan 08 '19 at 22:56
@user3335081 Your desired results is a little unclear. Are the values literals or are they columns? — John Cappelletti, Jan 08 '19 at 22:56
There's a solution in there with `PARSENAME`, that supports up to 4. Are you sure you read all the solutions? — Thom A, Jan 08 '19 at 22:57
i am looking as columns. I am trying to insert into a table like below insert into @TempTable Select Today,Test,12456 — RDeveloper, Jan 08 '19 at 23:02
Possible duplicate of [Sub string by character from Table Type](https://stackoverflow.com/questions/44171037/sub-string-by-character-from-table-type) — SMor, Jan 08 '19 at 23:12
Yes, clearly it is a duplicate with the one that @SMor told [item 44171037](https://stackoverflow.com/questions/44171037/sub-string-by-character-from-table-type) — Angel M., Jan 09 '19 at 07:00
@AngelM. duplicate link you provided select only 2 columns and i expecting 3 columns. It's not duplicate — RDeveloper, Jan 09 '19 at 14:13

John Cappelletti · Accepted Answer · 2019-01-09T02:43:01.660

OK, from what I gather from the comments.

Here are two options. The XML approach and ParseName(). ParseName() will fail if you have periods within the text and/or more than 4 items

declare @st1 varchar(50) = 'Today+Test+12456'

Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
From  (Select Cast('<x>' + replace((Select replace(@st1,'+','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 



Select Pos1 = parsename(tStr,3)
      ,Pos2 = parsename(tStr,2)
      ,Pos3 = parsename(tStr,1)
From  ( values(replace(@st1,'+','.'))) B1(tStr)

Both would return

Pos1    Pos2    Pos3
Today   Test    12456

EDIT

I see that you've updated the question.

If you REALLY wanted to go Dynamic SQL, it could be as simple as

CREATE TABLE #TempTable (
Id INT IDENTITY,
col1 varchar(50), 
col2 varchar(50), 
col3 nvarchar(50)
);


Declare @st1 varchar(50) = 'Today+Test+12456'
Declare @SQL varchar(max) = 'Insert Into #TempTable values ('''+replace(@st1,'+',''',''')+''')'
Exec(@SQL)

@user3335081 Please see EDIT. That convoluted dynamic SQL is not necessary — John Cappelletti, Jan 09 '19 at 02:44

score 1 · Answer 2 · answered Jan 08 '19 at 23:24

May be it helps

DECLARE @input NVARCHAR(50) = 'Today+Test+12456+'
DECLARE @ColumnValue NVARCHAR(100)
DECLARE @Index INT
DECLARE @SelectString   [nvarchar] (500) = 'SELECT ' 
 WHILE CHARINDEX('+', @input) > 0
 BEGIN
    SELECT @Index  = CHARINDEX('+', @input)  
    SELECT @ColumnValue = SUBSTRING(@input, 1, @Index-1)
    Set @SelectString = @SelectString + '''' +@ColumnValue + ''', '
    SELECT @input = SUBSTRING(@input, @Index + 1, LEN(@input) - @Index)
 END
SELECT @SelectString = SUBSTRING(@SelectString,0, LEN(@SelectString))
EXEc sp_sqlexec @SelectString

Angel M. · Answer 3 · 2019-01-09T06:12:19.563

0

Not sure if I understood properly or your definition needs some clarification but... I think the following query will solve your request:

DECLARE @st1 VARCHAR(50) = 'Today+Test+12456';
INSERT INTO @TempTable
SELECT 'SELECT ' + REPLACE(@st1, '+', ', ');

edited Jan 09 '19 at 06:12

answered Jan 08 '19 at 22:58

Angel M.

1,360
8
17

No. i am looking to insert into @TempTable select Today, Test, 12456 – RDeveloper Jan 08 '19 at 23:01

SQL query to spit from string

3 Answers3