September 27, 2012 at 11:00 pm
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
September 28, 2012 at 12:13 am
we are not sure what exactly you are looking for?
September 28, 2012 at 12:22 am
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
September 28, 2012 at 12:29 am
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
September 28, 2012 at 1:02 am
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 (............................)
September 28, 2012 at 1:23 am
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
September 28, 2012 at 1:44 am
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 ?
September 28, 2012 at 2:19 am
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
September 28, 2012 at 3:49 am
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)
September 28, 2012 at 4:29 am
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
September 28, 2012 at 5:50 am
thanks
September 28, 2012 at 5:54 am
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
September 28, 2012 at 6:18 am
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
October 1, 2012 at 12:39 am
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 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