Last post Oct 23, 2015 01:31 AM by Krunal Parekh
Oct 22, 2015 06:10 AM|swgdesign|LINK
I am hoping someone can help me out with the logic for this as I am stuck and all the split functions don't seem to do what I require them to do, or I am missing something completely.
In my ASP.Net application I have a STRING (RoutingNumbers) that could be a single number "47" OR it could be a comma delimited string of numbers ie "46,47,48" or "81,84,88,101".
What I need to do is to check that these number(s) exist in TableA.MatchingIDs; MatchingIDs is an nvarchar column containing either a single number or a comma delimited string of numbers as with and return the value for column 'NextNumber.
The amount of numbers in either the string or db column can vary from null to 10+.
Routingnumbers = 46,48
TableA.MatchingIds = 46,47,48
TableA.nextNumber = 12
This would return 12
Oct 22, 2015 10:26 AM|limno|LINK
Combine int as a comma delimited list as string is not a good design. But if you cannot change that, you need to use a UDF split to separate your integers as individual value and compare both list after split.
You can search to find T-SQL SPLIT UDF from search easily.
Oct 23, 2015 01:31 AM|Krunal Parekh|LINK
Please try this.
Declare @Test As Table
INSERT INTO @Test
select '46,47,48', 12
DECLARE @Routingnumbers VARCHAR(max) = '46,48'
DECLARE @RoutingCount INT = (SELECT COUNT(TokenID) FROM dbo.udf_Split(@Routingnumbers,','))
CASE WHEN COUNT(T.nextnumber) = @RoutingCount THEN 1 ELSE 0 END IsAllMatched -- this flag will be true only if all the numbers match
FROM @Test T
SELECT Match FROM dbo.udf_Split(T.MatchingIds,',')
WHERE ',' + @Routingnumbers + ',' LIKE '%,' + O.Match + ',%'
GROUP BY T.nextnumber
nextnumber MatchedNumber RoutingCount IsAllMatched
12 2 2 1
The split function.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE FUNCTION [dbo].[udf_Split]
@InputText VARCHAR(MAX), -- The text to be split into rows
@Delimiter VARCHAR(10) -- The delimiter that separates tokens. Can be multiple characters, or empty
RETURNS @Array TABLE
TokenID Int PRIMARY KEY IDENTITY(1,1), --Comment out this line if you don't want the identity column
-- Initialize the variables.
-- Len ignores trailing spaces, thus the use of DataLength.
-- Note: if you switch to NVarchar input and output, you'll need to divide by 2.
-- Length of input text
DECLARE @TextLength INT = DATALENGTH(@InputText)
-- Exit function if no text is passed in.
IF @TextLength = 0 RETURN
-- Start of token or character
DECLARE @Pos INT = 1
-- End of token
DECLARE @End INT
-- Length of delimiter
DECLARE @DelimLength INT = DATALENGTH(@Delimiter)
IF @DelimLength = 0
BEGIN -- Each character in its own row
WHILE @Pos <= @TextLength BEGIN
INSERT @Array (Match) VALUES (SUBSTRING(@InputText,@Pos,1))
SET @Pos = @Pos + 1
-- Tack on delimiter to 'see' the last token
SET @InputText = @InputText + @Delimiter
-- Find the end character of the first token
SET @End = CHARINDEX(@Delimiter, @InputText)
WHILE @End > 0 BEGIN
-- End > 0, a delimiter was found: there is a(nother) token
INSERT @Array (Match) VALUES (SUBSTRING(@InputText, @Pos, @End - @Pos))
-- Set next search to start after the previous token
SET @Pos = @End + @DelimLength
-- Find the end character of the next token
SET @End = CHARINDEX(@Delimiter, @InputText, @Pos)