TSQL Translate ChangeData Field in CRM 2011

Question

I have a delimited string which acts as a key for a target delimited string. I need to know where 2 values are in the key sequence, and then pull only those positions from my target string.

Example 1
Key : ,15,90,104,73,95,13,14,
Target : Medium~Male~28~Green~Right~No~10/04/2013

Example 2
Key : ,14,73,104,95,15,13,90,
Target : 12/03/2013~Green~28~Right~Medium~No~Male~

I only care about the gender and date values in the target and the corresponding entries in the key will always be 14 and 90

Once I know where those values are sequentially (positions 2 & 7 in example 1), I need to pull out the same sections of my target string so I end up with the gender and the date values in their own variables

I have changed the type of data I'm looking for to make the question easier to understand

I hope that made sense

Thanks

Matt

score 0 · Answer 1 · edited May 23 '17 at 12:29

0

You can split the two strings (see here: Split function equivalent in T-SQL?).

Then, when you have splitted the strings in two table objects, access at the N-th record (e.g. using the TOP keyword) in both and you can get the result.

Given the two strings and an int you can write a function to get the two coupled values.

edited May 23 '17 at 12:29

Community

1
1

answered Apr 10 '13 at 09:35

Andrea Colleoni

5,919
3
30
49

The only problem with that solution is each time a target and key are generated the length can vary drastically so there is no way to know the number of columns I need. I was hoping that it would be possible to store the sequential value of in a variable and then use that variable to extract the relevant section from the target – Powell21 Apr 10 '13 at 09:39
If I can understand well your comment and reading again your question, I think once you have splitted your values in a table (variable, temporary or physical it doesn't matter) as the function does, you can get the nth record of that table. See here: http://stackoverflow.com/questions/1022514/selecting-nth-record-in-an-sql-query – Andrea Colleoni Apr 10 '13 at 10:10
But regardless of the type of table I use won't I have to define a number of columns? I won't know how many columns I need because the amount of values in my string will alter – Powell21 Apr 10 '13 at 10:16
Sorry, I didn't pay enough attention to your use of the word 'columns'. With that kind of function, your strings ar splitted in rows, not columns, so you should not have that problem. – Andrea Colleoni Apr 10 '13 at 10:21

score 0 · Accepted Answer · answered Apr 10 '13 at 10:24

Try this solution -

DECLARE @temp TABLE
(
      id INT IDENTITY(1,1)
    , k NVARCHAR(100)
    , t NVARCHAR(500)
)

INSERT INTO @temp (k, t)
VALUES 
    (',15,90,104,73,95,13,14,', 'Medium~Male~28~Green~Right~No~10/04/2013'),
    (',14,73,104,95,15,13,90,', '12/03/2013~Green~28~Right~Medium~No~Male~')

SELECT 
      data.id
    , data.p
    , data.r 
FROM (
    SELECT 
          p = p.value('(.)[1]', 'NVARCHAR(50)')
        , po = p.value('for $i in . return count(../*[. << $i])', 'int')
        , r = r.value('(.)[1]', 'NVARCHAR(50)')
        , ro = r.value('for $i in . return count(../*[. << $i])', 'int')
        , d.id
    FROM (
        SELECT 
              t.id
            , txml = CAST('<r><s>' + REPLACE(t.k + ',', ',', '</s>' + '<s>') + '</s></r>' AS XML)
            , kxml = CAST('<r><s>' + REPLACE(t.t + '~', '~', '</s>' + '<s>') + '</s></r>' AS XML)  
        FROM @temp t
    ) d
    CROSS APPLY kxml.nodes('/r/s') t(p)
    CROSS APPLY txml.nodes('/r/s') k(r)
) data
WHERE data.po = data.ro - 1
    AND data.r IN ('14', '90')
    --AND r + data.p != ''
ORDER BY data.id

I didn't mean to un-accept this solution. I thought I could accept 2 simultaneously — Powell21, Apr 16 '13 at 13:55

score 0 · Answer 3 · answered Apr 11 '13 at 07:54

As a quick Follow up, I had trouble applying the above solution (probably due to a lack of knowledge on my part) so I created the following functions and they do the job

GET SEQUENTIAL VALUE ----------------------------------------------------------------------

DECLARE @Attributemask varchar(max) = '[PASS IN KEY STRING]' 
DECLARE @Required varchar(10) = '[PASS IN VALUE TO LOOK FOR FOLLOWED BY A COMMA]'
DECLARE @AttPosition int
DECLARE @TempString varchar(max)
DECLARE @Result as int

SET @AttPosition = CHARINDEX(@Required, @Attributemask)
SET @TempString = LEFT(@Attributemask, @AttPosition)
SET @Result = len(@TempString)-len(replace(@TempString,',',''))
SELECT @Result

GET DATA ----------------------------------------------------------------------------------

DECLARE @ChangeData varchar(max) = '[PASS IN TARGET STRING]'
DECLARE @AttPos int = [PASS IN RESULT FROM PREVIOUS FUNCTION] 
DECLARE @Count int = 1
DECLARE @NotNeeded varchar(100)
Declare @Result varchar(max)

SET @ChangeData = '~'+@ChangeData

WHILE @Count < (@AttPos +1)
BEGIN
    SET @NotNeeded = CHARINDEX('~', @ChangeData)
    SET @ChangeData = right(@ChangeData,len(@ChangeData) - @NotNeeded)
    IF len(@ChangeData) = 0 BREAK
    SET @Count = @Count +1
END

SET @Result = LEFT(@ChangeData, (CHARINDEX('~', @ChangeData)))
SET @Result = LEFT(@Result, (LEN(@Result)-1))
Select @Result

TSQL Translate ChangeData Field in CRM 2011

3 Answers3