January 17, 2019 at 5:46 am
Dear All,
In my scenario, i need to generate string like
AAA,AAB,AAC.........ZZZ
My input parameter is just Total count of records
Example
declare @countRecord int
select @countRecord=count(*) from tablename
select dbo.udf(@countRecord)
i want output like
AAA
AAB
AAC
.
.
.
ZZZ
January 17, 2019 at 6:00 am
vs.satheesh - Thursday, January 17, 2019 5:46 AMDear All,In my scenario, i need to generate string like
AAA,AAB,AAC.........ZZZ
My input parameter is just Total count of records
Example
declare @countRecord intselect @countRecord=count(*) from tablename
select dbo.udf(@countRecord)
i want output like
AAA
AAB
AAC
.
.
.
ZZZ
CREATE FUNCTION ParseValues(@String varchar(8000), @Delimiter varchar(10) )RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(8000))ASBEGINDECLARE @Value varchar(100)WHILE @String is not nullBEGINSELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL ENDINSERT INTO @RESULTS (Val)SELECT @ValueENDRETURNEND
Reference link:
https://visakhm.blogspot.com/2010/02/parsing-delimited-string.html
Saravanan
January 17, 2019 at 6:41 am
Function:CREATE FUNCTION dbo.udf(@RowCount int)
RETURNS TABLE
AS
RETURN
WITH X AS (SELECT * FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26)) T(N))
SELECT TOP(@RowCount)
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',a.N,1)+SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',b.N,1)+SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',c.N,1) [Value]
FROM X a, X b, X c
ORDER BY 1
GO
Usage:DECLARE @countRecord int = 100
SELECT *
FROM dbo.udf(@countRecord)
January 17, 2019 at 10:23 pm
Jonathan AC Roberts - Thursday, January 17, 2019 6:41 AMFunction:CREATE FUNCTION dbo.udf(@RowCount int)
RETURNS TABLE
AS
RETURN
WITH X AS (SELECT * FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26)) T(N))
SELECT TOP(@RowCount)
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',a.N,1)+SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',b.N,1)+SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',c.N,1) [Value]
FROM X a, X b, X c
ORDER BY 1
GO
Usage:DECLARE @countRecord int = 100
SELECT *
FROM dbo.udf(@countRecord)
Dear Jonathan
Thanks for your reply. My client is using SQl server 2008 . I want only one value . because I called user defined function inside the loop.
January 17, 2019 at 10:47 pm
vs.satheesh - Thursday, January 17, 2019 10:23 PMJonathan AC Roberts - Thursday, January 17, 2019 6:41 AMFunction:CREATE FUNCTION dbo.udf(@RowCount int)
RETURNS TABLE
AS
RETURN
WITH X AS (SELECT * FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26)) T(N))
SELECT TOP(@RowCount)
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',a.N,1)+SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',b.N,1)+SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',c.N,1) [Value]
FROM X a, X b, X c
ORDER BY 1
GO
Usage:DECLARE @countRecord int = 100
SELECT *
FROM dbo.udf(@countRecord)Dear Jonathan
Thanks for your reply. My client is using SQl server 2008 . I want only one value . because I called user defined function inside the loop.
This will do thatDECLARE @countRecord int = 100
DECLARE @Result nvarchar(MAX) = ''
SELECT @Result = @Result + Value + CHAR(13) + CHAR(10)
FROM dbo.udf(@countRecord)
SELECT LEFT(@Result, LEN(@Result)-2)
January 18, 2019 at 10:01 am
This seems to perform much better, because it doesn't require an expensive SORT operation. It also uses the documented (and supported) XML concatenation over using the undocumented (and unsupported) quirky update method.
WITH Base AS (SELECT i FROM ( VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0) ) v(i) )
, Tally(n) AS ( SELECT ROW_NUMBER() OVER(ORDER BY @@VERSION ) - 1 FROM Base A, Base B, Base C )
SELECT STUFF(
(
SELECT TOP (@RowCount) ',' + CHAR(65 + n/26/26%26) + CHAR(65 + n/26%26) + CHAR(65 + n%26)
FROM Tally
FOR XML PATH(''), TYPE
).value('.', 'VARCHAR(MAX)'), 1, 1, '');
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 18, 2019 at 12:44 pm
No need to use resources to recompute the string every time.
Pre-generate all the strings and store them in a permanent table. Then just pull out the row(s) you need. Very fast, with a trivial-to-generate exec plan.
This method also provides a very easy way to skip certain letter/string combinations later. It's likely someone may not want see "FUK" as a string on a screen and/or document.
CREATE TABLE dbo.alpha_sequences
(
alpha_count smallint CONSTRAINT alha_sequence__PK PRIMARY KEY,
alpha_sequence char(3) NOT NULL
)
;WITH cte_numbers AS (SELECT * FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26)) numbers(number))
INSERT INTO dbo.alpha_sequences ( alpha_count, alpha_sequence )
SELECT
n1.number + ((n2.number-1) * 26) + ((n3.number-1) * 26 * 26) AS alpha_count,
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',n3.number,1) +
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',n2.number,1) +
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',n1.number,1) AS alpha_sequence
FROM cte_numbers n1
CROSS JOIN cte_numbers n2
CROSS JOIN cte_numbers n3
ORDER BY 1
GO
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION dbo.getAlphaSequence (
@count smallint
)
RETURNS TABLE
AS
RETURN (
SELECT TOP (@count) alpha_sequence
FROM dbo.alpha_sequences
ORDER BY alpha_count
)
GO
SELECT *
FROM dbo.getAlphaSequence(10)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 18, 2019 at 3:16 pm
vs.satheesh - Thursday, January 17, 2019 5:46 AMDear All,In my scenario, i need to generate string like
AAA,AAB,AAC.........ZZZ
My input parameter is just Total count of records
Example
declare @countRecord intselect @countRecord=count(*) from tablename
select dbo.udf(@countRecord)
i want output like
AAA
AAB
AAC
.
.
.
ZZZ
You don't actually want to do this. There are some pretty offensive words that can be spelled out just in English never mind all of the languages of the world.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 18, 2019 at 3:34 pm
Jeff Moden - Friday, January 18, 2019 3:16 PMYou don't actually want to do this. There are some pretty offensive words that can be spelled out just in English never mind all of the languages of the world.
Yes, it would be even worse if the OP wanted 4 letter codes.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply