I need to compare and split the string and then save it in the detail_tb but dont know how ?

  • Some one post this code in forum and it work fine except i didnt got any clue,How to map these values to insert them in my detail_tb:

    MESSAGE_DETAILS_TB :

    -----------

    CREATE TABLE msgDetailIn_Tb ( msgdetails_Id int

    IDENTITY(1,1),

    fk_visbox_Id int NOT NULL,

    fk_msgIn_Id int NOT NULL,

    mvoltage varchar(50),

    mnorth varchar(50),

    meast varchar(50),

    mtime varchar(50),

    mtemperature varchar(50),

    mheight varchar(50),

    mcompraser varchar(50),

    mluman varchar(50),

    PRIMARY KEY (msgdetails_Id)

    );

    Code to split string :

    DECLARE @CHARACTERS TABLE (CHARS CHAR(1))

    INSERT INTO @CHARACTERS VALUES

    ('N'), ('E'), ('M'), ('H'), ('T'), ('V'), ('L'), ('C' );

    DECLARE @STRING VARCHAR(500);

    SET @STRING= '2449.555N06704.2855EM0701H071T44.098V11.764L0.372C1';

    DECLARE @len int;

    SET @len =LEN(@STRING);

    IF(@len > 0)

    BEGIN

    WITH CTE AS (

    SELECT CAST(REVERSE(LEFT(REVERSE(LEFT(@STRING,

    CHARINDEX(CHARS,@STRING,1)-1)), PATINDEX('%[^0-9,.]%',

    REVERSE(LEFT(@STRING,CHARINDEX(CHARS,@STRING,1)-1)) + 'Z')-1))

    AS VARCHAR(50)) AS RESULT

    FROM @CHARACTERS )

    SELECT * FROM CTE WHERE LEN(RESULT)>2

    END

    Output :

    2449.7183

    06704.2855

    0701

    071

    44.098

    11.764

    0.372

    1

    Kindly help

  • we are not sure what exactly you are looking for?

  • I need to break the string which a is a status message generated randomly from a machine connected to a GSM device,which i had done successfully and know i need to store the string in my table,whose code is mentioned above but dont know how to do it.

    Kindly let me as soon as possible

  • I take it you want to pivot the results so that they are on a single row rather than on several rows.

    DECLARE @CHARACTERS TABLE (CHARS CHAR(1))

    INSERT INTO @CHARACTERS VALUES

    ('N'), ('E'), ('M'), ('H'), ('T'), ('V'), ('L'), ('C' );

    DECLARE @STRING VARCHAR(500);

    SET @STRING= '2449.555N06704.2855EM0701H071T44.098V11.764L0.372C1';

    DECLARE @len int;

    SET @len =LEN(@STRING);

    IF(@len > 0)

    BEGIN

    WITH CTE AS (

    SELECT CHARINDEX(CHARS,@STRING,1) x

    , CAST(REVERSE(LEFT(REVERSE(LEFT(@STRING,

    CHARINDEX(CHARS,@STRING,1)-1)), PATINDEX('%[^0-9,.]%',

    REVERSE(LEFT(@STRING,CHARINDEX(CHARS,@STRING,1)-1)) + 'Z')-1))

    AS VARCHAR(50)) AS RESULT

    FROM @CHARACTERS )

    Select

    pid,MAX([1]) Col1,MAX([2]) Col2,MAX([3]) Col3,MAX([4]) Col4,MAX([5]) Col5,MAX([6]) col6,MAX([7]) col7

    From (

    SELECT 1 pid,ROW_NUMBER() OVER(Order by x) pivotcol,* FROM CTE WHERE LEN(RESULT)>2) s

    pivot(max(RESULT) FOR pivotcol in ([1],[2],[3],[4],[5],[6],[7])) pvt

    group by pid

    END

    This will generate a resultset of : pid,Col1,Col2,Col3,Col4,Col5,col6,col7

    If the string has more than 7 elements then you will need to extend the pivot and outer select to handle it.

    Edit : typos and column list.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • Where and how to map my table column name with the values that are splitted from the code :

    INSERT INTO @details_Tb(mvoltage,mnorth,meast,mtime,mtemperature,mheight,mcompraser,mluman)

    VALUES (............................)

  • Its a fairly straight forward Insert, the issue you will have is that you need to define the fk_visbox_id and fk_msgIn_id columns otherwise the Insert will fail as these are non-nullable columns as per your msg_detailIn_Tb DDL.

    ;WITH CTE AS (

    SELECT CHARINDEX(CHARS,@STRING,1) x

    , CAST(REVERSE(LEFT(REVERSE(LEFT(@STRING,

    CHARINDEX(CHARS,@STRING,1)-1)), PATINDEX('%[^0-9,.]%',

    REVERSE(LEFT(@STRING,CHARINDEX(CHARS,@STRING,1)-1)) + 'Z')-1))

    AS VARCHAR(50)) AS RESULT

    FROM @CHARACTERS

    )

    Insert Into @details_Tb

    (mvoltage,mnorth,meast,mtime,mtemperature,mheight,mcompraser,mluman)

    Select

    MAX([1]) Col1,MAX([2]) Col2,MAX([3]) Col3,MAX([4]) Col4,MAX([5]) Col5,MAX([6]) col6,MAX([7]) col7,MAX([8]) col8

    From (

    SELECT 1 pid,ROW_NUMBER() OVER(Order by x) pivotcol,Result FROM CTE WHERE LEN(RESULT)>2) s

    pivot(Max(RESULT) FOR pivotcol in ([1],[2],[3],[4],[5],[6],[7],[8])) pvt

    group by pid

    Ps : Sorry I noticed I missed the last column.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • those two foriegn key column can map,If i know how to map others .

    I select ka fk column values by selcting them with some criteria and map theses key with variable and pas them to insert queries.

    DECLARE

    @visid int,

    @msginID int,

    @@gsmno nvarchar(5)

    Select @msginID=fk_msgIn_Id,@visid=fk_visbox_Id from messageIn_Tb where GSMno =@gsmno

    I want to map the values as i did in above code.Is it possible and how ?

  • If you select those values into variables, then the Insert is very simple,

    WITH CTE AS (

    SELECT CHARINDEX(CHARS,@STRING,1) x

    , CAST(REVERSE(LEFT(REVERSE(LEFT(@STRING,

    CHARINDEX(CHARS,@STRING,1)-1)), PATINDEX('%[^0-9,.]%',

    REVERSE(LEFT(@STRING,CHARINDEX(CHARS,@STRING,1)-1)) + 'Z')-1))

    AS VARCHAR(50)) AS RESULT

    FROM @CHARACTERS )

    Insert Into msgDetailIn_Tb

    (fk_visbox_Id, fk_msgIn_id,mvoltage,mnorth,meast,mtime,mtemperature,mheight,mcompraser,mluman)

    Select

    @visid,@msginID ,MAX([1]) Col1,MAX([2]) Col2,MAX([3]) Col3,MAX([4]) Col4,MAX([5]) Col5,MAX([6]) col6,MAX([7]) col7,MAX([87]) col8

    From (

    SELECT 1 pid,ROW_NUMBER() OVER(Order by x) pivotcol,* FROM CTE WHERE LEN(RESULT)>2) s

    pivot(max(RESULT) FOR pivotcol in ([1],[2],[3],[4],[5],[6],[7],[8])) pvt

    group by pid

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • code resolve my problem regarding insertion except it split Luman i.e. 0.372 value from string and saved in mcompraser column i.e. 1 in given string,and shows null in mluman colum in table :

    OUTPUT:

    Idmnorth meast mtimemheight mtemperaturemvoltagemcompraser mluman

    6 2449.555 06704.67880701071 44.678 11.7640.372 NULL

    7 2449.555 06704.67880701071 44.678 11.7640.372 NULL

    Message :

    (8 row(s) affected)

    Warning: Null value is eliminated by an aggregate or other SET operation.

    (1 row(s) affected)

  • I've just debugged it and theres a fault in the original CTE not parsing the last value in the list.

    so I've 'tweaked' it to add on a terminator, such that it will pick up the value for X, without impacting the rest of the query.

    DECLARE @CHARACTERS TABLE (CHARS VARCHAR(2))

    INSERT INTO @CHARACTERS VALUES

    ('N'), ('E'), ('M'), ('H'), ('T'), ('V'), ('L'), ('C'), ('XX');

    DECLARE @STRING VARCHAR(500);

    SET @STRING= '2449.555N06704.2855EM0701H071T44.098V11.764L0.372C1'+'XX';

    DECLARE @visid int=1,@msginID int=2

    DECLARE @len int;

    SET @len =LEN(@STRING);

    IF(@len > 0)

    BEGIN

    WITH CTE AS (

    SELECT CHARINDEX(CHARS,@STRING,1) x

    , CAST(REVERSE(LEFT(REVERSE(LEFT(@STRING,

    CHARINDEX(CHARS,@STRING,1)-1)), PATINDEX('%[^0-9,.]%',

    REVERSE(LEFT(@STRING,CHARINDEX(CHARS,@STRING,1)-1)) + 'Z')-1))

    AS VARCHAR(50)) AS RESULT

    FROM @CHARACTERS

    )

    Insert Into msgDetailIn_Tb

    (fk_visbox_Id, fk_msgIn_id,mvoltage,mnorth,meast,mtime,mtemperature,mheight,mcompraser,mluman)

    Select

    @visid,@msginID ,MAX([1]) Col1,MAX([2]) Col2,MAX([3]) Col3,MAX([4]) Col4,MAX([5]) Col5,MAX([6]) col6,MAX([7]) col7,MAX([8]) col8

    From (SELECT 1 pid,ROW_NUMBER() OVER(Order by x) pivotcol,*

    FROM CTE

    WHERE LEN(RESULT)>0) s

    pivot(max(RESULT)

    FOR pivotcol in ([1],[2],[3],[4],[5],[6],[7],[8])) pvt

    group by pid

    END

    THere is another issue, on the original you limit the query LEN(Result)>2 however the length of C is 1 so you'd never get it returned anyway, hence the change to WHERE LEN(RESULT)>0

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • thanks

  • Thanks alot for responding and I check its perfect but do i need to add 'XX' to my string as My string is a message from another table ?

    I will do somthing like this :

    Select msg+'XX' from messageIn_TB where ..........

    Is this a corrrect way to do that ? I asking coz all the splitting of string is done when a INSERT TRIGGER is fired on MessageIN_Tb and I didnt tried that right now .....

    My thread link:

    http://www.sqlservercentral.com/Forums/Topic1365750-392-1.aspx?Update=1

  • I've seen that thread I'm not a fan of triggers on tables (personal preference) so hardly ever use them, but that doesnt mean its a bad thing.

    It does need the +'XX' (or some other terminating character) as that was the only way I could get it to terminate the string and return the last data column.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • Here's an interesting approach using a generic string splitter (DelimitedSplit8K) that can be found here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

    DECLARE @CHARACTERS TABLE (CHARS CHAR(1))

    INSERT INTO @CHARACTERS VALUES

    ('N'), ('E'), ('M'), ('H'), ('T'), ('V'), ('L'), ('C' );

    DECLARE @STRINGS TABLE (STRING VARCHAR(500));

    INSERT INTO @STRINGS

    SELECT '2449.555N06704.2855EM0701H071T44.098V11.764L0.372C1';

    ;WITH rCTE AS (

    SELECT STRING, n, ItemNumber, Item

    FROM @STRINGS

    INNER JOIN (

    SELECT n=ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),CHARS

    FROM @CHARACTERS) a ON n=1

    CROSS APPLY dbo.DelimitedSplit8K(STRING, CHARS)

    UNION ALL

    SELECT STRING, b.n+1, c.ItemNumber, c.Item

    FROM rCTE b

    INNER JOIN (

    SELECT n=ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),CHARS

    FROM @CHARACTERS) a ON a.n = b.n + 1

    CROSS APPLY dbo.DelimitedSplit8K(Item, CHARS) c

    WHERE b.ItemNumber <> 1

    )

    SELECT mvoltage=MAX(CASE WHEN n=1 THEN Item ELSE NULL END)

    ,mnorth=MAX(CASE WHEN n=2 THEN Item ELSE NULL END)

    ,meast=MAX(CASE WHEN n=3 THEN Item ELSE NULL END)

    ,mtime=MAX(CASE WHEN n=4 THEN Item ELSE NULL END)

    ,mtemperature=MAX(CASE WHEN n=5 THEN Item ELSE NULL END)

    ,mheight=MAX(CASE WHEN n=6 THEN Item ELSE NULL END)

    ,mcompraser=MAX(CASE WHEN n=7 THEN Item ELSE NULL END)

    ,mluman=MAX(CASE WHEN n=8 THEN Item ELSE NULL END)

    FROM rCTE

    WHERE ItemNumber = 1

    GROUP BY STRING

    Forgive me Jeff... Just having a bit of fun.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply