SQL query to print each character of a string : SQL server

Question

Trying to write a query for printing each character of a string separately. I have tried the following

select substring('sas',1,1)
union all
select substring('sas',2,1)
union all
select substring('sas',3,1)

But I would have to run union all for each character. Any better approach to this?

Sandbox: http://sqlfiddle.com/#!9/9eecb/123683

Probably some help is here - https://stackoverflow.com/questions/19136697/split-a-string-without-delimiter-in-tsql — mkRabbani, Dec 19 '19 at 10:34
Does this answer your question? [Split a string without delimiter in TSQl](https://stackoverflow.com/questions/19136697/split-a-string-without-delimiter-in-tsql) — Amira Bedhiafi, Dec 19 '19 at 10:37
You've tagged 2 completely unsupported versions of SQL Server here. 2008 ran out of support this summer, and 2005 has been out of support for years, You 8really* should be looking at upgrade paths asap. 2005 certainly has known security vulnerabilities. — Thom A, Dec 19 '19 at 10:58
Now you've removed those tags. So what version of SQL Server are you actually using..? — Thom A, Dec 19 '19 at 11:08

unknown · Accepted Answer · 2020-01-19T09:00:56.093

4

DECLARE @data VARCHAR(100) = 'October 11, 2017'   
;WITH CTE AS 
(
    SELECT STUFF(@data,1,1,'') TXT, LEFT(@data,1) Col1
    UNION ALL
    SELECT STUFF(TXT,1,1,'') TXT, LEFT(TXT,1) Col1 FROM CTE
    WHERE LEN(TXT) > 0
)
select Col1, ISNUMERIC(Col1) from CTE

edited Jan 19 '20 at 09:00

answered Dec 19 '19 at 10:31

unknown

322
6
25

You can write a table function based on this CTE as well. – Max Zolotenko Dec 19 '19 at 10:44
Couple of points of this. `ISNUMERIC` is a really poor function. I realise that the OP initially tagged 2005/2008 but it's use should be avoided. It provides both false positives and negatives. Considering the OP has removed those tags, `TRY_CONVERT` would be a much better function now. Also `;` isn't part of a `WITH` expression, it's a statement **terminator**; it goes at the end of every statement. *You also have the fact that this is an iterative task, and so will be slower with much longer strings.* – Thom A Dec 19 '19 at 19:04

score 2 · Answer 2 · answered Dec 19 '19 at 11:13

2

You can try this as well

DECLARE @data VARCHAR(100) = 'TEST'

Declare @cnt int = len(@data)
Declare @i int =1
While (@i <= @cnt)
BEGIN

PRint SUBSTRING(@data,@i,1)


set @i=@i+1


END

answered Dec 19 '19 at 11:13

Red Devil

2,343
2
21
41

A `WHILE` will be really slow here. By far the slowest way to achieve this. – Thom A Dec 19 '19 at 11:46
that's a useful script that works – givonz Feb 17 '22 at 17:45

Thom A · Answer 3 · 2019-12-19T19:00:32.120

1

I really don't like the use of an rCTE for tasks like this, that are iterative and slow (far slower than a Tally, especially when more than a few rows). You could use a Tally and do this far faster. As a TVF, this would like like this:

CREATE FUNCTION dbo.GetChars (@String varchar(8000))
RETURNS table
AS RETURN
    WITH N AS(
        SELECT N
        FROM(VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
    Tally AS(
        SELECT TOP (LEN(@String)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM N N1, N N2, N N3, N N4)
    SELECT SUBSTRING(@String, T.I, 1) AS C, T.I
    FROM Tally T;
GO

db<>fiddle

Note, this will not work on SQL Server 2005.

edited Dec 19 '19 at 19:00

answered Dec 19 '19 at 10:49

Thom A

88,727
11
45
75

Trying to compile this I got error: "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified" – Max Zolotenko Dec 19 '19 at 11:38
That's a simple fix @МаксимЗолотенко ; the error tells you what to do, but will demonstrate. – Thom A Dec 19 '19 at 11:44

SQL query to print each character of a string : SQL server

3 Answers3