Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

I need to compare and split the string and then save it in the detail_tb but dont know how ? Expand / Collapse
Author
Message
Posted Thursday, September 27, 2012 11:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 7:39 AM
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
Post #1365604
Posted Friday, September 28, 2012 12:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 18, 2013 1:47 AM
Points: 196, Visits: 87
we are not sure what exactly you are looking for?
Post #1365627
Posted Friday, September 28, 2012 12:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 7:39 AM
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
Post #1365631
Posted Friday, September 28, 2012 12:29 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 953, Visits: 2,626
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
Post #1365632
Posted Friday, September 28, 2012 1:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 7:39 AM
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 (............................)
Post #1365638
Posted Friday, September 28, 2012 1:23 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 953, Visits: 2,626
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
Post #1365648
Posted Friday, September 28, 2012 1:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 7:39 AM
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 ?
Post #1365657
Posted Friday, September 28, 2012 2:19 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 953, Visits: 2,626
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
Post #1365677
Posted Friday, September 28, 2012 3:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 7:39 AM
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)
Post #1365723
Posted Friday, September 28, 2012 4:29 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 953, Visits: 2,626
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
Post #1365753
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse