August 15, 2017 at 12:35 pm
Hi I have a filed in one of the tables and Following is the sample data in this column.
Field ID FieldDesc
1 Department ='test' AND Branch = 'Yankee' AND city = 'Brooklyn' AND State = 'NY' and country = 'USA' and Zipcode = 11223
I need to get all the strings before the symbol '='.
In the above string all I need to get is
Field ID FieldDesc
1 Department
1 Branch
1 City
1 State
1 County
1 Zipcode
can someone help with the query to get the above data.
Thanks.
August 15, 2017 at 1:04 pm
You need Jeff Moden's DelimitedSplit8K function for this...
DECLARE @MyString VARCHAR(200) = 'Department =''test'' AND Branch = ''Yankee'' AND city = ''Brooklyn'' AND State = ''NY'' and country = ''USA'' and Zipcode = ''11223''';
SELECT @MyString = REPLACE(@MyString,' AND ','|'); -- necessary because the Splitter function he wrote only takes a single character (well, unless I'm doing it wrong).
SELECT spl.ItemNumber
, spl.Item
, LEFT(spl.Item, CHARINDEX('=',spl.Item) - 1) AS Loc
FROM Scratchpad.dbo.DelimitedSplit8K(@MyString,'|') spl;
If you need these permanently separated, you could wrap the above query in an INSERT...
August 16, 2017 at 12:24 am
Hope it helps!
IF OBJECT_ID('tempdb..#tmpTest') IS NOT NULL DROP TABLE #tmpTest ;
CREATE TABLE #tmpTest
(
Id INT IDENTITY(1, 1) NOT NULL
, Data VARCHAR(MAX) NOT NULL
) ;
INSERT INTO #tmpTest
SELECT 'Department =''test'' AND Branch = ''Yankee'' AND city = ''Brooklyn'' AND State = ''NY'' and country = ''USA'' and Zipcode = ''11223'''
UNION ALL
SELECT 'Department1 =''test'' AND Branch1 = ''Yankee'' AND city1 = ''Brooklyn'' AND State1 = ''NY'' and country1 = ''USA'' and Zipcode1 = ''11223'''
UNION ALL
SELECT 'Department2 =''test'' AND Branch2 = ''Yankee'' AND city2 = ''Brooklyn'' AND State2 = ''NY'' and country2 = ''USA'' and Zipcode2 = ''11223''' ;
WITH xmlRawDataCTE AS
(
SELECT Id = tmp.Id
, XmlData = CAST(REPLACE('<Row><Item>' + tmp.data + '</Item></Row>', '=', '</Item></Row><Row><Item>') AS XML)
FROM #tmpTest tmp
)
, dataCTE AS
(
SELECT [Field ID] = d.Id
, [Field Desc] = REVERSE(LEFT(REVERSE(x.ColumnName), CASE WHEN CHARINDEX(' ', REVERSE(x.ColumnName)) <= 0 THEN LEN(x.ColumnName) ELSE CHARINDEX(' ', REVERSE(x.ColumnName)) -1 END))
, ReverseSerialNumber = ROW_NUMBER() OVER(PARTITION BY d.Id ORDER BY x.SerialNumber DESC)
FROM xmlRawDataCTE d
CROSS APPLY
(
SELECT [ColumnName] = LTRIM(RTRIM(Node.Data.value('(Item)[1]', 'VARCHAR(100)')))
, SerialNumber = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM d.XmlData.nodes('/Row') Node(Data)
) x
)
SELECT d.[Field ID]
, d.[Field Desc]
FROM dataCTE d
WHERE ReverseSerialNumber > 1
ORDER BY d.[Field ID], d.ReverseSerialNumber DESC ;
August 23, 2017 at 1:02 pm
You can use STRING_SPLIT function to split the values and use outer apply to join with function. I have used 100 in the FieldVal column to parse to avoid one more function, but you can use LEN(Value) as well.
create table #Info
(
id int
,FieldDesc varchar(1000)
)
insert into #Info values
(1, 'Department =''test'' AND Branch = ''Yankee'' AND city = ''Brooklyn'' AND State = ''NY'' and country = ''USA'' and Zipcode = 11223')
,(2, 'Department =''test2'' AND Branch = ''Branch2'' AND city = ''city2'' AND State = ''NY'' and country = ''USA'' and Zipcode = 11335')
select a.id,a.FieldDesc, left(b.value,charindex('=',b.value)-1) FieldType, ltrim(substring(b.value,charindex('=',b.value)+1,100)) FieldVal
from #Info a
outer apply STRING_SPLIT(replace(a.FieldDesc,' AND ','|'),'|') b
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply