I need to compare and split the string and then save it in the detail

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » I need to compare and split the string and...

14 posts, Page 1 of 2

1 2 »»

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

Rate Topic

Display Mode

Topic Options

Author

Message

maida_rh

Posted Thursday, September 27, 2012 11:00 PM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 11:58 PM
Points: 42, Visits: 97

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 :

Kindly help

Post #1365604

vivekkumar341

Posted Friday, September 28, 2012 12:13 AM

SSC-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

maida_rh

Posted Friday, September 28, 2012 12:22 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 11:58 PM
Points: 42, Visits: 97

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

Jason-299789

Posted Friday, September 28, 2012 12:29 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:30 AM
Points: 803, Visits: 2,124

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

maida_rh

Posted Friday, September 28, 2012 1:02 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 11:58 PM
Points: 42, Visits: 97

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

Jason-299789

Posted Friday, September 28, 2012 1:23 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:30 AM
Points: 803, Visits: 2,124

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

maida_rh

Posted Friday, September 28, 2012 1:44 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 11:58 PM
Points: 42, Visits: 97

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

Jason-299789

Posted Friday, September 28, 2012 2:19 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:30 AM
Points: 803, Visits: 2,124

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

maida_rh

Posted Friday, September 28, 2012 3:49 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 11:58 PM
Points: 42, Visits: 97

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

Jason-299789

Posted Friday, September 28, 2012 4:29 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:30 AM
Points: 803, Visits: 2,124

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 »

14 posts, Page 1 of 2

1 2 »»

Permissions