InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 12:51:00 GMT20http://www.sqlservercentral.com/Forums/Topic1365604-392-1.aspxHere's an interesting approach using a generic string splitter (DelimitedSplit8K) that can be found here: [url]http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url][code="sql"]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 @STRINGSSELECT '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 rCTEWHERE ItemNumber = 1GROUP BY STRING[/code]Forgive me Jeff... Just having a bit of fun.Mon, 01 Oct 2012 00:39:37 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1365604-392-1.aspxI'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.Fri, 28 Sep 2012 06:18:18 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1365604-392-1.aspxThanks 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:[url]http://www.sqlservercentral.com/Forums/Topic1365750-392-1.aspx?Update=1[/url]Fri, 28 Sep 2012 05:54:16 GMTmaida_rhhttp://www.sqlservercentral.com/Forums/Topic1365604-392-1.aspxthanksFri, 28 Sep 2012 05:50:05 GMTmaida_rhhttp://www.sqlservercentral.com/Forums/Topic1365604-392-1.aspxI'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.[code="sql"]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=2DECLARE @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 pidEND[/code]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)>0Fri, 28 Sep 2012 04:29:29 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1365604-392-1.aspxcode 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:[code="sql"]Id mnorth meast mtime mheight mtemperature mvoltage mcompraser mluman6 2449.555 06704.6788 0701 071 44.678 11.764 0.372 NULL7 2449.555 06704.6788 0701 071 44.678 11.764 0.372 NULL[/code]Message :(8 row(s) affected)Warning: Null value is eliminated by an aggregate or other SET operation.(1 row(s) affected)Fri, 28 Sep 2012 03:49:58 GMTmaida_rhhttp://www.sqlservercentral.com/Forums/Topic1365604-392-1.aspxIf you select those values into variables, then the Insert is very simple,[code="sql"]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 RESULTFROM @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]) col8From (SELECT 1 pid,ROW_NUMBER() OVER(Order by x) pivotcol,* FROM CTE WHERE LEN(RESULT)>2) spivot(max(RESULT) FOR pivotcol in ([1],[2],[3],[4],[5],[6],[7],[8])) pvtgroup by pid[/code]Fri, 28 Sep 2012 02:19:02 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1365604-392-1.aspxthose 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.[code="sql"]DECLARE @visid int,@msginID int,@@gsmno nvarchar(5)Select @msginID=fk_msgIn_Id,@visid=fk_visbox_Id from messageIn_Tb where GSMno =@gsmno[/code]I want to map the values as i did in above code.Is it possible and how ?Fri, 28 Sep 2012 01:44:17 GMTmaida_rhhttp://www.sqlservercentral.com/Forums/Topic1365604-392-1.aspxIts 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. [code="sql"];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 RESULTFROM @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]) col8From (SELECT 1 pid,ROW_NUMBER() OVER(Order by x) pivotcol,Result FROM CTE WHERE LEN(RESULT)>2) spivot(Max(RESULT) FOR pivotcol in ([1],[2],[3],[4],[5],[6],[7],[8])) pvtgroup by pid[/code]Ps : Sorry I noticed I missed the last column.Fri, 28 Sep 2012 01:23:06 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1365604-392-1.aspxWhere 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 (............................)Fri, 28 Sep 2012 01:02:42 GMTmaida_rhhttp://www.sqlservercentral.com/Forums/Topic1365604-392-1.aspxI take it you want to pivot the results so that they are on a single row rather than on several rows.[code="sql"]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)BEGINWITH 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 RESULTFROM @CHARACTERS )Select pid,MAX([1]) Col1,MAX([2]) Col2,MAX([3]) Col3,MAX([4]) Col4,MAX([5]) Col5,MAX([6]) col6,MAX([7]) col7From (SELECT 1 pid,ROW_NUMBER() OVER(Order by x) pivotcol,* FROM CTE WHERE LEN(RESULT)>2) spivot(max(RESULT) FOR pivotcol in ([1],[2],[3],[4],[5],[6],[7])) pvtgroup by pidEND[/code]This will generate a resultset of : pid,Col1,Col2,Col3,Col4,Col5,col6,col7If 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.Fri, 28 Sep 2012 00:29:00 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1365604-392-1.aspxI 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 possibleFri, 28 Sep 2012 00:22:40 GMTmaida_rhhttp://www.sqlservercentral.com/Forums/Topic1365604-392-1.aspxwe are not sure what exactly you are looking for?Fri, 28 Sep 2012 00:13:45 GMTvivekkumar341http://www.sqlservercentral.com/Forums/Topic1365604-392-1.aspxSome 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 :-----------[code="sql"]CREATE TABLE msgDetailIn_Tb ( msgdetails_Id intIDENTITY(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]Code to split string :[code="sql"]DECLARE @CHARACTERS TABLE (CHARS CHAR(1))INSERT INTO @CHARACTERS VALUES('N'), ('E'), ('M'), ('H'), ('T'), ('V'), ('L'), ('C' );DECLARE @STRING VARCHAR(500);SET @STRING= '[b]2449.555N06704.2855EM0701H071T44.098V11.764L0.372C1[/b]';DECLARE @len int;SET @len =LEN(@STRING);IF(@len > 0)BEGINWITH 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 RESULTFROM @CHARACTERS )SELECT * FROM CTE WHERE LEN(RESULT)>2END[/code]Output :[code="other"]2449.7183 06704.2855 070107144.09811.7640.3721[/code]Kindly helpThu, 27 Sep 2012 23:00:53 GMTmaida_rh