January 18, 2017 at 7:54 am
Hye,
SELECT CHARINDEX('A2,C1,C2,C3,D1', '01,02,03,04,05,A1,A2,C1,C2,C3,D1,D2,
J1,J2,J3,K1,M1,M2,M3,N1,N2,N3,P1,P2,P3,R1,S1,S2,T1,T2,T3,W1,W2,W3') AS [Location]
Location
19
SELECT CHARINDEX('A2,R1,T3,D1', '01,02,03,04,05,A1,A2,C1,C2,C3,D1,D2,
J1,J2,J3,K1,M1,M2,M3,N1,N2,N3,P1,P2,P3,R1,S1,S2,T1,T2,T3,W1,W2,W3') AS [Location]
Location
0
Why A2,R1,T3,D1 return 0? A2,R1,T3,D1 is Exists in String
Please Help
January 18, 2017 at 7:59 am
Qira - Wednesday, January 18, 2017 7:54 AMHye,
SELECT CHARINDEX('A2,C1,C2,C3,D1', '01,02,03,04,05,A1,A2,C1,C2,C3,D1,D2,
J1,J2,J3,K1,M1,M2,M3,N1,N2,N3,P1,P2,P3,R1,S1,S2,T1,T2,T3,W1,W2,W3') AS [Location]Location
19SELECT CHARINDEX('A2,R1,T3,D1', '01,02,03,04,05,A1,A2,C1,C2,C3,D1,D2,
J1,J2,J3,K1,M1,M2,M3,N1,N2,N3,P1,P2,P3,R1,S1,S2,T1,T2,T3,W1,W2,W3') AS [Location]Location
0Why A2,R1,T3,D1 return 0? A2,R1,T3,D1 is Exists in String
Please Help
The string 'A2,R1,T3,D1' doesn't exist. If you want to check for individual elements, you'll need to use a splitter function.
John
January 18, 2017 at 8:00 am
Qira - Wednesday, January 18, 2017 7:54 AMHye,
SELECT CHARINDEX('A2,C1,C2,C3,D1', '01,02,03,04,05,A1,A2,C1,C2,C3,D1,D2,
J1,J2,J3,K1,M1,M2,M3,N1,N2,N3,P1,P2,P3,R1,S1,S2,T1,T2,T3,W1,W2,W3') AS [Location]Location
19SELECT CHARINDEX('A2,R1,T3,D1', '01,02,03,04,05,A1,A2,C1,C2,C3,D1,D2,
J1,J2,J3,K1,M1,M2,M3,N1,N2,N3,P1,P2,P3,R1,S1,S2,T1,T2,T3,W1,W2,W3') AS [Location]Location
0Why A2,R1,T3,D1 return 0? A2,R1,T3,D1 is Exists in String
Please Help
Your string 'A2,R1,T3,D1' does not exist in '01,02,03,04,05,A1,A2,C1,C2,C3,D1,D2,J1,J2,J3,K1,M1,M2,M3,N1,N2,N3,P1,P2,P3,R1,S1,S2,T1,T2,T3,W1,W2,W3')
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
January 18, 2017 at 9:14 am
John Mitchell-245523 - Wednesday, January 18, 2017 7:59 AMQira - Wednesday, January 18, 2017 7:54 AMHye,
SELECT CHARINDEX('A2,C1,C2,C3,D1', '01,02,03,04,05,A1,A2,C1,C2,C3,D1,D2,
J1,J2,J3,K1,M1,M2,M3,N1,N2,N3,P1,P2,P3,R1,S1,S2,T1,T2,T3,W1,W2,W3') AS [Location]Location
19SELECT CHARINDEX('A2,R1,T3,D1', '01,02,03,04,05,A1,A2,C1,C2,C3,D1,D2,
J1,J2,J3,K1,M1,M2,M3,N1,N2,N3,P1,P2,P3,R1,S1,S2,T1,T2,T3,W1,W2,W3') AS [Location]Location
0Why A2,R1,T3,D1 return 0? A2,R1,T3,D1 is Exists in String
Please Help
The string 'A2,R1,T3,D1' doesn't exist. If you want to check for individual elements, you'll need to use a splitter function.
John
The splitter John linked to is the best one around in T-SQL. There's a native function in SQL 2016, but it doesn't (yet) provide the same functionality as Jeff's.
January 19, 2017 at 1:12 pm
Qira - Wednesday, January 18, 2017 7:54 AMHye,
SELECT CHARINDEX('A2,C1,C2,C3,D1', '01,02,03,04,05,A1,A2,C1,C2,C3,D1,D2,
J1,J2,J3,K1,M1,M2,M3,N1,N2,N3,P1,P2,P3,R1,S1,S2,T1,T2,T3,W1,W2,W3') AS [Location]Location
19SELECT CHARINDEX('A2,R1,T3,D1', '01,02,03,04,05,A1,A2,C1,C2,C3,D1,D2,
J1,J2,J3,K1,M1,M2,M3,N1,N2,N3,P1,P2,P3,R1,S1,S2,T1,T2,T3,W1,W2,W3') AS [Location]Location
0Why A2,R1,T3,D1 return 0? A2,R1,T3,D1 is Exists in String
Please Help
As others have posted, CHARINDEX looks for the exact string value you supply to occur in the string you search within, so while individual values within the comma delimited string exist in the string to be searched, CHARINDEX isn't going to solve your problem. The question is just what kind of location value are you going to want when the string segments appear other than continuously within the string you're searching? It just seems likely that you may well have a table where a column contains the values to be searched, and the strings to search for might be in yet another table, so I'm pretty sure you're going to need a rather different approach. Here's a little something using the string splitter most other posters have referred to:
DECLARE @SEARCH_PARAM_1 AS varchar(100) = 'A2,C1,C2,C3,D1';
DECLARE @SEARCH_PARAM_2 AS varchar(100) = 'A2,R1,T3,D1';
DECLARE @DATA_TABLE AS TABLE (
DATA_STRING varchar(255)NOT NULL PRIMARY KEY CLUSTERED
);
INSERT INTO @DATA_TABLE (DATA_STRING)
VALUES ('01,02,03,04,05,A1,A2,C1,C2,C3,D1,D2,J1,J2,J3,K1,M1,M2,M3,N1,N2,N3,P1,P2,P3,R1,S1,S2,T1,T2,T3,W1,W2,W3');
WITH SEARCH_DATA AS (
SELECT 1 AS SearchParamNumber, S1.Item, S1.ItemNumber
FROM dbo.DelimitedSplit8K(@SEARCH_PARAM_1, ',') AS S1
UNION ALL
SELECT 2, S2.Item, S2.ItemNumber
FROM dbo.DelimitedSplit8K(@SEARCH_PARAM_2, ',') AS S2
),
DATA_TABLE AS (
SELECT *
FROM @DATA_TABLE AS DT
CROSS APPLY dbo.DelimitedSplit8K(DT.DATA_STRING, ',') AS S
)
SELECT S.SearchParamNumber, S.ItemNumber AS SearchItemNumber, S.Item AS SearchValue,
T.ItemNumber AS DataItenNumber, T.DATA_STRING AS SourceString
FROM SEARCH_DATA AS S
LEFT OUTER JOIN DATA_TABLE AS T
ON S.Item = T.Item
ORDER BY S.SearchParamNumber, T.ItemNumber;
Let us know if this kind of approach might help, and/or clarify what you really want, given the complications of what you appear to want to accomplish...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply