DECLARE
@String VARCHAR(
MAX
)
SET
@String =
'abc'
;
SELECT
SUBSTRING
(@String, 1 + Number, 1) [
char
] , number
AS
[Array
Index
]
FROM
master..spt_values
WHERE
Number < DATALENGTH(@String)
AND
type =
'P'
/*
Create
sample
table
*/
IF OBJECT_ID(
'tempdb..#Text'
,
'U'
IS
NOT
NULL
DROP
TABLE
#Test;
CREATE
#Test
(
ID
INT
IDENTITY(1, 1) ,
Phrase
VARCHAR
(255)
);
/*Populate the
with
sample data*/
INSERT
( Phrase )
VALUES
'CHICAGO'
),
'NEW YORK'
'HOUSTON'
'SAN FRANCISCO'
/*This
is
the final solution*/
WITH
base
L.[
] ,
T.ID ,
T.Phrase
#Test T
CROSS
APPLY (
(T.Phrase, 1 + Number, 1) [
Number < DATALENGTH(T.Phrase)
) L
DISTINCT
b1.Phrase ,
REPLACE
((
''
+ [
base b2
b1.Phrase = b2.Phrase
ORDER
BY
[
FOR
XML PATH(
' '
' '
columns2
b1;
Naomi N edited Revision 8. Comment: Added toc and one more link to See Also
Saeid Hasani edited Revision 7. Comment: Add See Also section.
Naomi N edited Revision 6. Comment: Minor edit
This article may benefit from the See Also section
Thanks,
You are right. I will add it soon.
Thanks Naomi!!!
You added a table of content! Now it looks great.
Please add your other T-SQL articles to this parent article social.technet.microsoft.com/.../17785.sql-server-query-language-transact-sql.aspx and also encourage others to do so or add other T-SQL articles you know which are not yet in that page. I want that page to be a catalog of all T-SQL articles. We may also create a similar article for SSIS or SSRS and make sure to add it to see also section in related articles.
Good idea. I also will add my next articles.
Thanks Naomi for adding "T-SQL Guru Gold Medal Winner" tag.
But it's not shown in this tag page:
social.technet.microsoft.com/.../default.aspx
Congrats on winning the gold medal: blogs.technet.com/.../technet-guru-awards-august-2013.aspx
Thanks