Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
maida_rh
maida_rh
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 111
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
vivekkumar341
vivekkumar341
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 93
we are not sure what exactly you are looking for?
maida_rh
maida_rh
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 111
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
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 3229
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
maida_rh
maida_rh
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 111
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 (............................)
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 3229
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
maida_rh
maida_rh
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 111
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 ?
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 3229
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
maida_rh
maida_rh
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 111
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:


Id   mnorth meast mtime   mheight mtemperature   mvoltage   mcompraser    mluman

6 2449.555 06704.6788   0701   071    44.678    11.764   0.372    NULL
7 2449.555 06704.6788   0701   071    44.678    11.764   0.372    NULL



Message :

(8 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected)
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 3229
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
   Wink
   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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search