Trying to Make combinations within a bracket in SSMS column
-
Hey Steve, I do not have fnTally as a function in my version of SSMS, My SQL Server is 110 compatible as is version 17
-
Cl 110 is equivalent to SQL Server 2012 iirc
This link has the implementation which (according to Jeff's notes in the function) should work for SQL Server 2008 and above
https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
- riddhi10 wrote:
Also Brian, can we think about breaking this string down into various columns such as A[bcd] will be broken down to A,[,b,c,d,].... and then we cross apply A with all others ?
would that work?
Kind of, but that isn't really helping anything by approaching this with the string split function. For the first one all that string_split will give you is:
A
[
b
c
d
]
and it might not even be in that order (one of the risks of string_split is the order can be anything). If it comes back out of order, how do you now which character to start with? If you are using String_Split, you'd want your input to be something more like A[a,b,c] but even then it isn't going to be any easier.
I think the Tally Table approach is your best bet for performance (ie set based operations). I personally like Phil's approach over making a new "single use" function for this (fnTally) as it is very similar and I don't need to build a special function for it. That being said, if you have multiple use cases for a tally table (which you might), the function can be handy.
Also, I made a mistake - Phil's approach works perfectly... I made a typo when copy-pasting from the email notification version of his script. I should know better than to question his scripts!
My preference would probably still be to do this at the application layer not the database layer, but now it is less about performance and more about where my "data manipulation" occurs. My preference is that the database side should not be doing data modification prior to returning the results to the calling application. There are exceptions to that (if I want to know the cost of a product that was purchased in USD and my system is in CAD but stores the daily USD exchange rate for example), but for the majority of database calls (especially string manipulation), I prefer to handle it all application side.
Here, my thought is that at some point they may add extra rules on such as ranges or more than 2 character resulting strings. If this is handled inside a single C# class file (StringManipulation.cs or similar), then when additional functionality is required, you can add it into a single class to handle the different cases. You have a main function that takes a string input and it strips off the first character then passes the rest to a "parser" function. That parser function looks at the string inside the [ ]'s and passes it off to the appropriate parser or parsers if there are more than 1 that need to be called such as if the value is something odd like A[aceg:k^h^i] which would be for Aa, Ac, Ae, Ag, Aj, Ak.
Doing the whole thing in SQL, while possible, may not be practical long term.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
drop table if exists dbo.test_TID;
go
create table dbo.test_TID(
TIDPrefix varchar(20) not null);
insert dbo.test_TID(TIDPrefix) values
('A[123xyz]'),
('x[UY345]'),
('XP');
drop function if exists dbo.fnTally;
go
CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Jeff Moden Script on SSC: https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
**********************************************************************************************************************/
(@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
H2(N) AS ( SELECT 1
FROM (VALUES
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
)V(N)) --16^2 or 256 rows
, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
SELECT TOP(@MaxN)
N = ROW_NUMBER() OVER (ORDER BY N)
FROM H8
;
GO
select
tt.TIDPrefix,
iif(tt.TIDPrefix like '%/[%/]' escape '/', concat(left(tt.TIDPrefix,1), substring(tt.TIDPrefix, n+2, 1)), tt.TIDPrefix) Expanded_TIDPrefix
from
dbo.test_TID tt
cross apply
dbo.fnTally(1, iif(tt.TIDPrefix like '%/[%/]' escape '/', len(tt.TIDPrefix)-3, 1)) t;Results
TIDPrefixExpanded_TIDPrefix
A[123xyz]A1
A[123xyz]A2
A[123xyz]A3
A[123xyz]Ax
A[123xyz]Ay
A[123xyz]Az
x[UY345]xU
x[UY345]xY
x[UY345]x3
x[UY345]x4
x[UY345]x5
XP XPAus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Ok here's a tvf which maybe makes it more maintainable and readable. Fwiw I don't follow Brian's approach and keeping data manipulation logic in the database seems OK imo. To which C# project type would it belong? An application or an API or a library?
drop function if exists dbo.fnTIDprefix_Expand;
go
create function dbo.fnTIDprefix_Expand(
@TIDprefix varchar(20))
returns table as
return
select
iif(@TIDprefix like '%/[%/]' escape '/', concat(left(@TIDprefix, 1), substring(@TIDprefix, n+2, 1)), @TIDprefix) TIDprefix_Expanded
from
dbo.fnTally(1, iif(@TIDprefix like '%/[%/]' escape '/', len(@TIDprefix)-3, 1)) t;
go
/* final query */
select
t.TIDPrefix,
tx.TIDprefix_Expanded
from
dbo.test_TID t
cross apply
dbo.fnTIDprefix_Expand(t.TIDprefix) tx;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
I am denied the permission to create the function here
-
Phil's solution works well. It's more flexible in that there could be more than 1 character to prefix the []'s
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
My approach is to do as little data manipulation on the SQL side as possible before presenting it to the end user or application. I'm not saying that it is the "best" approach, I just don't like changing the underlying data before presenting it to the end user. The data is my "source of truth". Changing the source of truth before presenting it to an end user means that they are relying on the logic I did to change things to be accurate. And if the client application has a change in design that results in the calculation changing (such as a requirement for handling A[a:g]), it is easier for the application developer to handle that on their end than for me to keep changing the stored procedure call to meet their ever changing needs.
Apart from scope creep, my approach is also to try to reduce load on the server and put more load on the client machines. Client desktops are generally quite fast at doing things. Not that the database isn't, but if I can offload the work to a client machine rather than have it on the servers, I can use the server cycles for other things. Now, it isn't always the best idea to toss this back as a client side operation; there is a tradeoff for sure. I don't want my client side application sitting there for 5-10 minutes doing calculations when I can do it in 5-10 seconds server side. But if performance on the client side is comparible to the server side, I would much rather push it off to the client to process. Also, the faster the query completes, the less blocking will occur.
As for the C# project type, it depends on how it is being used. If it is a single application that looks at this data and will always be that single application, I'd have a single class for it inside that application. If there are multiple applications, it would be in a library that is shared between the applications. I personally wouldn't use an API for this as that feels like extra unneeded overhead, unless it was a security thing such as the database does not allow users to connect to it directly and has a "middleware" application that other applications talk to to ask for database data. I designed an API like that (windows service) that listens for TCP/IP traffic on a specific port and a specific command. Once it gets the correct command and verifies its origins, it connects to the database and pulls out the requested information, encrypts it and sends it back. The database has very few users who can connect, but the windows service is allowed to. developed entirely in-house and requires a special USB key to be plugged into the source computer to decrypt anything, so it is a pretty secure setup.
But that's just my approach. I've seen applications that use the agile programming methodology result in a lot of changes as they go; which is by design of Agile. The downside is this can get messy on the database side pretty quickly especially if your database developers are not the same crew as the application developers OR if they are the same group but they lack TSQL skills and you end up with a lot of row-based operations happening in your database because the C# doesn't really know how to do set based operations.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. - riddhi10 wrote:
What application would you suggest using for this, If i were to run row operations on the application side?
None. As you've seen by Phil's and Steve's solutions, T-SQL solutions work just fine (possible needing a tweak).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - riddhi10 wrote:
I am denied the permission to create the function here
Submit the fnTally function to the resident DBA for review and deployment. Be sure to include the header so they know it's had the hell tested out of it. If they're a regular reader here, chances are good they may already have it installed but under a different name.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Just to throw my hat into the ring, here's my humble version... It uses the fnTally function that Steve posted.
First, here's the test data I used...
DROP TABLE IF EXISTS #TestTable
;
--===== Create a test table and populate it.
-- This is NOT a part of the solution.
-- We're just creating test data here.
SELECT v.TIDPrefix
INTO #TestTable
FROM (VALUES
('A[kfg]')
,('B[lr]')
,('Cf')
,('D[3r]')
,('ABC[123]')
)v(TIDPrefix)
;
SELECT * FROM #TestTable
;Here's my solution...
WITH ctePosit AS
(--==== "Cheater" method to handle just about anything
SELECT TIDPrefix
,LeftBower = CHARINDEX('[',TIDPrefix+'[')
,RightBower = CHARINDEX(']',TIDPrefix+'[]')
FROM #TestTable
)--==== This gabs the "header" and each character and concatenates them together as requested.
SELECT TIDPrefix = p.TIDPrefix
,TIDExpanded = CONCAT(LEFT(p.TIDPrefix,LeftBower-1), SUBSTRING(p.TIDPrefix,LeftBower+t.N,1))
FROM ctePosit p
OUTER APPLY dbo.fnTally(1,RightBower-LeftBower-1)t
;Here are the results...
If the DBA won't deploy the fnTally function for you, post back and I'll show you how to do it kind of the same way Phil did but with shorter code to make the inline cte that replaces the fnTally function. It's actually shorter than the function Steve posted.
The reason why the function that Steve posted is so long was to seriously reduce CROSS JOINs so people don't have a heart attack when they see the execution plan for it.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Shifting gears a bit, please read the first link in my signature line below for how to post example data in future posts to help us help you faster and better
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Mr. Brian Gale wrote:
My recommendation is to do this application side still, but if database side is the solution you need, this query should do it:
DECLARE @tmp TABLE
(
[Letters] VARCHAR(25)
);
INSERT INTO @tmp
(
[Letters]
)
VALUES
(
'A[kfg]'-- Letters - varchar(25)
)
, (
'B[lr]'
)
, (
'Cf'
)
, (
'D[3r]'
);
DECLARE @letter VARCHAR(25);
DECLARE @startLetter CHAR(1);
DECLARE @result TABLE
(
[Letters] CHAR(2)
);
DECLARE [cursewords] CURSOR LOCAL FAST_FORWARD FOR
SELECT
[Letters]
FROM@tmp;
OPEN [cursewords];
FETCH NEXT FROM [cursewords]
INTO
@letter;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT
@startLetter = LEFT(@letter, 1);
IF (CHARINDEX( '['
, @letter
)
) = 0
BEGIN
INSERT INTO @result
VALUES
(
@letter
);
END;
ELSE
BEGIN
DECLARE @endValue INT;
DECLARE @counter INT = 2;
SELECT
@endValue = CHARINDEX( ']'
, @letter
) - 1;
WHILE @counter < @endValue
BEGIN
SELECT
@counter = @counter + 1;
INSERT INTO @result
(
[Letters]
)
SELECT
@startLetter + RIGHT(LEFT(@letter, @counter), 1);
END;
END;
FETCH NEXT FROM [cursewords]
INTO
@letter;
END;
SELECT
[Letters]
FROM@result;The reason I don't like this in the database side is it becomes a ROW based operation rather than a SET based operation. What I mean here is that it is going to be slow. SQL doesn't like working on ROW based operations which is why you should try to avoid loops (while and cursor) and that query needs 2. It is the "if all you own is a hammer, every problem looks like a nail" solution.
Now, doing this application side will put little strain on the SQL server as you just need to pull the data in to the application and then manipulate it as you need. Applications are better at working with loops and this puts no strain on any server; just on the client machine.
Not a slam, Brian... a suggestion. See the following articles. Remember that a declared cursor is really just a loop that you don't have to bump a counter on.
https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff, the DBA does not allow me the permission to create/use tally function. Can you provide a solution without using it please?
- riddhi10 wrote:
Hi Jeff, the DBA does not allow me the permission to create/use tally function. Can you provide a solution without using it please?
I wasn't suggesting that YOU create the function... I was suggesting that you have the DBA review the function and have them deploy the fnTally function for you. If they refuse, they should study set-based code more than they apparently have. 🙁
That, not withstanding, here's the code to do exactly the same thing using my previously posted test table except there's no functions required. You'll need to change the #TestTable in the ctePosit code to your table and, of course, you need to change the TIDPrefix column do do what you need with your real table.
WITH
H1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))H0(N))
,cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM H1 a,H1 b,H1 c,H1 d,H1 e,H1 f,H1 g,H1 h)
,ctePosit AS (--==== "Cheater" method to handle just about anything
SELECT TIDPrefix
,LeftBower = CHARINDEX('[',TIDPrefix+'[')
,RightBower = CHARINDEX(']',TIDPrefix+'[]')
FROM #TestTable
)--==== This grabs the "header" and each character and concatenates them together as requested.
SELECT TIDPrefix = p.TIDPrefix
,TIDExpanded = CONCAT(LEFT(p.TIDPrefix,LeftBower-1), SUBSTRING(p.TIDPrefix,LeftBower+t.N,1))
FROM ctePosit p
OUTER APPLY (SELECT TOP (RightBower-LeftBower-1) N FROM cteTally ORDER BY N) t
;--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 16 through 30 (of 71 total)
You must be logged in to reply to this topic. Login to reply