sql server 2012: function get position of given string and then get string of given position

Question

I have a table where there are 2 columns: optionValues and optionNames . optionNames contains the text of a combobox and optionValues the value of each text , both are separated with ~. For example :

optionValues | optionNames
   0~1       |   male~female

What I am trying is to create a function which will take as argument an optionValue and will return the optionName. Using the example above:

fn.GetOptionName(1) --will return `female`

How I can separate the values and get back the correct one?

This is what I started with but it wont work because it doesn't separate the values:

select @on = OptionNames from dbo.Table 
where tablename = @tablename and fieldname = @fieldname and optionvalues = @fieldvalue

(I cannot change the db schema no matter if its good or bad)

EDIT

Found this function which returns a substring in a specific position: DelimitedSplit8K

"cannot change the db schema no matter if its good or bad" - just in case you were in any doubt.... it's bad. Columns should contain atomic values. — Martin Smith, Jan 18 '18 at 08:42
@MartinSmith no doubt, thats why I wrote that ... But its not mine to change it — aggicd, Jan 18 '18 at 08:43
Do you have only one row in your table? How do you know to which option value belongs? — uzi, Jan 18 '18 at 08:46
@uzi there is also fieldname and tablename as you can see in my example. — aggicd, Jan 18 '18 at 08:47
@MartinSmith I dont think that is duplicate... the proposed question is a part of mine, it doesn't solve my question — aggicd, Jan 18 '18 at 08:48
You need a way to split a delimited string and return an item at specified position. That is what the duplicate asks. — Martin Smith, Jan 18 '18 at 08:50
@MartinSmith I want to get the position and return an item from another column at the specified position, not from the same — aggicd, Jan 18 '18 at 08:51
@MartinSmith So I need first to get the position of a given string and then get a string from a given position. I dont think that this is duplicate — aggicd, Jan 18 '18 at 08:58
first split your both columns. check https://stackoverflow.com/questions/48316918/sql-server-2012-function-get-name-from-value#48316918 then get the index of value from the value column. And finally fetch the option name using same index number from optionname column. You need to write store procedure for the same. — Gaurang Dave, Jan 18 '18 at 09:08
@GaurangDave thats my question. I know what I need to do but I dont know how I can do it in sql server — aggicd, Jan 18 '18 at 09:12
@aggicd , it is not that easy for someone to post code directly. Wait for it if it is implemented, someone will put code here. — Gaurang Dave, Jan 18 '18 at 09:13
You should probably *fix the broken design* rather than try to cover this up. This schema breaks the 1st Normal Form. This isn't an academic rule - you *can't* query data if you break this rule, not without a huge cost anyway. If the same options are used multiple times create a *separate* table with option IDs and values. If each row has completely different options and you *don't* need to query them that often, use an XML field. At least this way you can use XML functions to extract the values. Filtering by them would still be expensive — Panagiotis Kanavos, Jan 18 '18 at 09:47
@PanagiotisKanavos I know but I can't , I found it like this and the whole system is built on this schema, I am not allowed to change it. — aggicd, Jan 18 '18 at 09:54

Pawan Kumar · Accepted Answer · 2018-01-18T10:23:26.020

Please try this ( SQL 2016 + )

Data Generation

CREATE TABLE Splits
(
     optionValues varchar(20)
    ,optionNames varchar(200)
)
GO

INSERT INTO Splits VALUES
('0~1','male~female'),
('0~1~2','male~female~Trans'),
('0~1','male~F')
GO

SOLUTION

DECLARE @Find AS VARCHAR(10) = '1'
;WITH CTE0 AS
(
    SELECT * , ROW_NUMBER() OVER (ORDER BY ( SELECT NULL ) ) rnk  FROM Splits
)
,CTE AS 
(
    SELECT * , ROW_NUMBER() OVER (PARTITION BY rnk ORDER BY rnk) rnk1  FROM CTE0
    CROSS APPLY 
    (
        SELECT Value 
        FROM STRING_SPLIT(optionValues, '~'))
    p
)
,CTE1 AS
(
    SELECT * , ROW_NUMBER() OVER (PARTITION BY rnk ORDER BY rnk) rnk1 FROM CTE0
    CROSS APPLY 
    (
        SELECT Value 
        FROM STRING_SPLIT(optionNames, '~'))
    p1
)
SELECT s.*,ISNULL(p.value,'') Value FROM Splits s
LEFT JOIN 
(
    SELECT a.optionValues, a.optionNames , b.value FROM CTE a 
    INNER JOIN CTE1 b ON a.optionNames = b.optionNames
    AND a.optionValues = b.optionValues AND a.rnk = b.rnk AND a.rnk1 = b.rnk1
    WHERE a.value = @Find
)p ON p.optionValues = s.optionValues AND p.optionNames = s.optionNames

OUTPUT

optionValues         optionNames              Value
-------------------- ------------------------ ---------
0~1                  male~female              female
0~1~2                male~female~Trans        female
0~1                  male~F                   F

(3 rows affected)

Added for Solution for SQL 2012+

DECLARE @Find AS VARCHAR(10) = '1'
;WITH CTE0 AS
(
    SELECT * , ROW_NUMBER() OVER (ORDER BY ( SELECT NULL ) ) rnk  FROM Splits
)
,CTE AS 
(    
    SELECT * , ROW_NUMBER() OVER (PARTITION BY rnk ORDER BY rnk) rnk1 FROM 
    (
        SELECT *,CAST('<A>'+ REPLACE(optionValues,'~','</A><A>')+ '</A>' AS XML) po
        FROM CTE0
    )rt
    CROSS APPLY ( SELECT t.value('.', 'VARCHAR(10)') Value FROM po.nodes('/A') AS x(t) ) o
)
,CTE1 AS
(
    SELECT * , ROW_NUMBER() OVER (PARTITION BY rnk ORDER BY rnk) rnk1 FROM 
    (
        SELECT *,CAST('<A>'+ REPLACE(optionNames,'~','</A><A>')+ '</A>' AS XML) po
        FROM CTE0
    )rt
    CROSS APPLY ( SELECT t.value('.', 'VARCHAR(10)') Value FROM po.nodes('/A') AS x(t) ) o
)
SELECT s.*,ISNULL(p.value,'') Value FROM Splits s
LEFT JOIN 
(
    SELECT a.optionValues, a.optionNames , b.value , a.rnk FROM CTE a 
    INNER JOIN CTE1 b ON a.optionNames = b.optionNames
    AND a.optionValues = b.optionValues AND a.rnk = b.rnk AND a.rnk1 = b.rnk1
    WHERE a.value = @Find
)p ON p.optionValues = s.optionValues AND p.optionNames = s.optionNames
ORDER BY rnk

OUTPUT

optionValues         optionNames             Value
-------------------- ----------------------- ----------
0~1                  male~female             female
0~1~2                male~female~Trans       female
0~1                  male~F                  F

(3 rows affected)

No it will not. I am providing you a version for SQL 2012 in few minutes. — Pawan Kumar, Jan 18 '18 at 10:12
It looks that it is in the correct way but... I receive many empty rows before the Value... Is it possible to store the needed value in a variable? — aggicd, Jan 18 '18 at 10:40

score 0 · Answer 2 · answered Jan 18 '18 at 11:26

Try the below script

declare @fieldvalue NVARCHAR(10)    =   '1' --Set parameter value here
        ,@V_Value   NVARCHAR(100)  --local variable1
        ,@V_Name    NVARCHAR(100)  --local variable2


SELECT  @V_Value    =   optionValues
        ,@V_Name    =   optionNames
FROM    fulltable --tablename here
WHERE   '~'+optionvalues    LIKE '%~'+@fieldvalue+'%'
    AND tablename   =   @tablename 
    AND fieldname   =   @fieldname

SELECT  ROW_VALUE1,ROW_VALUE2
FROM    (
    SELECT  Split1.a.value('.', 'NVARCHAR(10)')         AS  ROW_VALUE1
            ,ROW_NUMBER() OVER(ORDER BY (SELECT 1))     AS  ROW_NO1
    FROM    (
            SELECT CAST('<X>'+REPLACE(@V_Value, '~', '</X><X>')+'</X>' AS XML) AS r1
        )   AS T1
        CROSS APPLY r1.nodes('/X') AS Split1(a)     
)   AS A
OUTER APPLY (
    SELECT   Split2.a.value('.', 'NVARCHAR(10)')        AS  ROW_VALUE2
            ,ROW_NUMBER() OVER(ORDER BY (SELECT 1))     AS  ROW_NO2
    FROM    (
            SELECT CAST('<X>'+REPLACE(@V_Name, '~', '</X><X>')+'</X>' AS XML) AS r2
        )   AS T1
        CROSS APPLY r2.nodes('/X') AS Split2(a) 
)   B
WHERE   ROW_VALUE1  =   @fieldvalue
    AND A.ROW_NO1   =   B.ROW_NO2

output:

ROW_VALUE1  ROW_VALUE2
1           female

hi, try to replace the `fieldvalue,tablename,fieldname` parameters and original table name and check it. — Abdul Rasheed, Jan 18 '18 at 11:36

sql server 2012: function get position of given string and then get string of given position

2 Answers2