in sql query right index is not working

Question

i treid your as follows SELECT farm_detailsdata , RIGHT ([farm_detailsdata] , CHARINDEX (' ' , REVERSE([farm_detailsdata]), 0)-0) as name FROM dbo.tbl_farmer_farmdetails_mozambique farmdetaildata Name {'Produto preferido para produção':'Algodão','Plantou algodão no ano passado?':'Sim','Irá':'Não'} When i run the above query i get output as follows Name Nao'} i want output as follows in each column Col1 Col2 Col3 Col4 Col5 Col6 Produto preferido para produção Algodão Plantou algodão no ano passado SIM Ira Nao farmdetaildata But i want the full text as metnioned above in my above sql query what changes i have to made to get full text. i think Right keyword should not used. which is the other way to use to get the full text.

Bryian Tan · Answer

You need to write much more code that what you currently posted here. You need a query to split the string by ";" and ",", query to cleanup the special characters and a query to pivot the rows to column. Luckily all the stuff that you need is available free online. Instead of going back and forth, I'll just share the query with you, but you need to promise to study it and do more reading. DECLARE @t TABLE ( ID INT IDENTITY ,data VARCHAR (4000) ) CREATE TABLE #r ( ID VARCHAR (100) ,data VARCHAR (4000) ) INSERT INTO @t (data) SELECT '{"Produto preferido para produção":"Algodão","Plantou algodão no ano passado?":"Sim","Irá":"Não"}' INSERT INTO #r SELECT O.Col ,O.splitdata FROM ( SELECT * , CAST ( ' ' + REPLACE ( REPLACE (f.[data] COLLATE SQL_Latin1_General_CP1_CS_AS, ':' , ' ' ), ',' , ' ' ) + ' ' AS XML) AS xmlfilter FROM @t F ) F1 CROSS APPLY ( SELECT 'Col' + CONVERT ( VARCHAR ,ROW_NUMBER() OVER( ORDER BY ( SELECT 1))) AS Col, REPLACE ( REPLACE ( REPLACE (fdata.D.value( '.' , 'varchar(1000)' ), '{' , '' ), '}' , '' ), '"' , '' ) AS splitdata FROM f1.xmlfilter.nodes( 'M' ) AS fdata(D) ) O WHERE LEN(O.splitdata) > 0 --SELECT * FROM #r DECLARE @cols AS NVARCHAR( MAX ), @query AS NVARCHAR( MAX ) select @cols = STUFF(( SELECT ',' + QUOTENAME(id) from #r group by id, [data] order by id FOR XML PATH( '' ), TYPE ).value( '.' , 'NVARCHAR(MAX)' ) ,1,1, '' ) set @query = N 'SELECT ' + @cols + N' from ( select id, [data] from #r ) x pivot ( max (data) for id in ( ' + @cols + N' ) ) p ' exec sp_executesql @query; DROP TABLE #r Output: Col1 Col2 Col3 Col4 Col5 Col6 Produto preferido para produção Algodão Plantou algodão no ano passado? Sim Irá Não https://www.mssqltips.com/sqlservertip/1771/splitting-delimited-strings-using-xml-in-sql-server/ https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server

in sql query right index is not working

Answers (1)