Need help to find character in String

  • 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

  • Qira - Wednesday, January 18, 2017 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

    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

  • Qira - Wednesday, January 18, 2017 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

    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

  • John Mitchell-245523 - Wednesday, January 18, 2017 7:59 AM

    Qira - Wednesday, January 18, 2017 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

    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.

  • Qira - Wednesday, January 18, 2017 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

    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