How to extract and split IDs from nvarchar column surrounded by spaces and a slash to compare to other table's value

Question

I feel my problem is very complicated.
I have a Table A with a column named AbsenceLinks.
The table would look like this:

- - - - - - - - - - - - - - - - - - - - - - - - - - - -
|     Description     |    AbsenceLinks  |
|- - - - - - - - - - - - - + - - - - - - - - - - - - - -|
|         Illness          |        14/3 15/9        |
|- - - - - - - - - - - - - + - - - - - - - - - - - - - -|
|     Education      |        19/3 18/9        |
|- - - - - - - - - - - - - + - - - - - - - - - - - - - -|
|Leave of Absence|            20/3           |
|- - - - - - - - - - - - - + - - - - - - - - - - - - - -|
|      Holiday         |             8/3             |
l- - - - - - - - - - - - - - - - - - - - - - - - - - - -l

I have another table B where I have a column named AbsenceID that matches the number before the slash-symbol in the AbsenceLinks column. (Table A AbsenceLinks value '20/9' matches AbsenceID 20 in table B)

This table would look like this:

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|          Absence           |       AbsenceID      |
| - - - - - - - - - - - - - - - -+ - - - - - - - - - - - - - -|
|        Illness (Days)      |             14               |
| - - - - - - - - - - - - - - - -+ - - - - - - - - - - - - - -|
|            Illness             |              15              |
| - - - - - - - - - - - - - - - -+ - - - - - - - - - - - - - -|
|    Leave of Absence   |             20              |
| - - - - - - - - - - - - - - - -+ - - - - - - - - - - - - - -|
|Holiday Without Salary|              8              |
l- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -l

I tried to see how I could retrieve some of the string from AbsenceLinks and made a case statement:

CASE
WHEN LEN(AbsenceLink) = 3 THEN SUBSTRING(AbsenceLink,1,1) --1/3
WHEN LEN(AbsenceLink) = 4 and SUBSTRING(AbsenceLink,1,4) LIKE '%/' THEN SUBSTRING(AbsenceLink,1,1)--1/10
WHEN LEN(AbsenceLink) = 4 AND SUBSTRING(AbsenceLink,1,4) LIKE '%/%' THEN SUBSTRING(AbsenceLink,1,2)--17/3
WHEN LEN(AbsenceLink) = 8 AND SUBSTRING(AbsenceLink,1,2) like '%/' AND SUBSTRING(AbsenceLink,5,2) like '%/' THEN SUBSTRING(AbsenceLink,1,1)+', '+SUBSTRING(AbsenceLink,5,1)--2/9 1/10
WHEN LEN(AbsenceLink) = 8 AND SUBSTRING(AbsenceLink,1,3) like '%/' AND SUBSTRING(AbsenceLink,5,3) like '%/' THEN SUBSTRING(AbsenceLink,1,2)+', '+SUBSTRING(AbsenceLink,5,2)--10/3 9/9
WHEN lenLENAbsenceLink) = 9 AND SUBSTRING(AbsenceLink,1,3) like '%/' AND SUBSTRING(AbsenceLink,5,4) like '%' THEN SUBSTRING(AbsenceLink,1,2)+', '+SUBSTRING(AbsenceLink,5,3)--14/3 15/9
End AS AbsLink

I have to compare theese values in a report for some statistic for a customer. I need theese two tables to be linked and theese columns are the only columns which can be linked.
I want to do like this:

SELECT A.col1, A.col2, B.col1, B.col2
FROM TableA A, TableB B
WHERE A.AbsenceLink = B.Absence

The problem is:

The value of AbsenceLink is a nvarchar value like '20 / 3 1/9 ', there may be one or many spaces before or after the AbsenceID
Absence is a int value like 20 and 1.
I want 20 and 1 from '20/3 1/9' to be compared and linked with the Absence value.
It is a database at my work and I can't change the data or make another table.

So dearest wise and clever programmers - what can I do?

Suggest you get rid of those ` `s with four spaces ahead instead. And post your expecting result on please. — Jaugar Chang, Sep 02 '14 at 09:49
Thank you. I'm not expecting a result as such. I just want to do like this: SELECT A.col1, A.col2, B.col2, B.col2 FROM TableA A, B TableB WHERE A.AbsenceLink = B.Absence But the value of AbsenceLink is a nvarchar value like '20 / 3 1/9 'and Absence is a int value like 20 and 1. I want 20 and one from '20 / 3 1/9 "to be compared and linked with the Absence value. — marinadelademad, Sep 02 '14 at 10:45

score 2 · Answer 1 · edited May 23 '17 at 11:53

UPDATE: Method with substring and join only.

There is a similar question expand-comma-separated-values-into-separate-rows answered by @KM. we could reference.

Your case is a little different from that one. You should take all the parts within some spaces before and a slash after. It's little harder than split string from a single character. But you can divide it into several steps to solve it.

Extract every part stopped by '/' into rows
- The method of @KM. works well of doing this.
Take the last part with spaces ahead.
- There may have spaces before the last '/', we need to find the position of the last space after trim the spaces before the last '/'. So we use the trick of revers like this charindex(' ', reverse(rtrim(left(AbsenceLink,number-1))),0).

Here is the result:SQLFiddle

with numbers as
(select 1 as number
union all
select number +1
from numbers
where number<100)
select 
 Description , 
 right(rtrim(left(AbsenceLink,number-1)),charindex(' ', reverse(rtrim(left(AbsenceLink,number-1))),0)) as AbsenceID
from
 (select Description, ' '+AbsenceLink as AbsenceLink
  from t) as t1
left join numbers
on number<= len(AbsenceLink)
where substring(AbsenceLink,number,1)='/'

NOTE:

If you care about the performance, create an permanent numbers table instead of temp table may be helpful.
More specific explain in this answer.

Method with CTE recursive query

SqlFiddle

with links as
(select 
  Description,
  substring(AbsenceLink,1,charindex('/',AbsenceLink, 0)-1) as AbsenceID,
  case when 
    charindex(' ',AbsenceLink, 0) > 0 then
    substring(AbsenceLink,charindex(' ',AbsenceLink, 0)+1,255)
  else '' end as left_links
from (select convert(varchar(255),ltrim(rtrim(substring(AbsenceLink,1,charindex('/',AbsenceLink, 0)-1) )) + '/' +ltrim(substring(AbsenceLink,charindex('/',AbsenceLink, 0)+1, 255) ) )as AbsenceLink,
             Description 
      from t) as t1
union all
select 
  Description,
  substring(left_links,1,charindex('/',left_links, 0)-1) as AbsenceID,
  case when 
    charindex(' ',left_links, 0) > 0 then
    substring(left_links,charindex(' ',left_links, 0)+1,255)
  else '' end as left_links
from (select convert(varchar(255),ltrim(rtrim(substring(left_links,1,charindex('/',left_links, 0)-1) )) + '/' +ltrim(substring(left_links,charindex('/',left_links, 0)+1, 255) ) )as left_links,
             Description             
      from links
      where left_links<>'') as t2
)
select 
 * from links
order by Description

With recursive CTE table, to get first link's absence id in every iteration until all links processed.
If there are more than one space before or after /, ltrim(rtrim(substring(AbsenceLink,1,charindex('/',AbsenceLink, 0)-1) )) + '/' + ltrim(substring(AbsenceLink,charindex('/',AbsenceLink, 0)+1, 255) ) can get rid off all the spaces before or after the first / in each recursive step.
To convert varchar to integer, can reference to SqlServer document here about convert function.

Two methods can get the same result like this:

DESCRIPTION   ABSENCEID 
Illness              14 
Illness              15 
Education            18 
Education            19 
Holiday               8 
Leave of Absence     20

score 0 · Answer 2 · answered Sep 02 '14 at 09:29

0

you can retrieve id with this query :

SELECT(SUBSTRING(AbsenceLink,0,CHARINDEX('/', AbsenceLink))) FROM table_name

and do your work

answered Sep 02 '14 at 09:29

Farrokh

1,167
1
7
18

That was a good one, but as a result, I'm missing the last number. Example: '14/3 15/9' this will only show number 14, it should also show number 15. I will figure it out with your solution. – marinadelademad Sep 02 '14 at 10:58

score 0 · Answer 3 · answered Sep 03 '14 at 04:21

OK dude, with this function you can split your field value and get the id

CREATE FUNCTION return_id(@Str VARCHAR(10), @part INT)
RETURNS INT
AS
BEGIN
    DECLARE @part1 VARCHAR(10),
            @part2 VARCHAR(10)

    DECLARE @Spc_Indx     INT,
            @retVal       INT

    SET @retVal = 0
    SET @part1 = @Str
    SET @part2 = ''

    IF CHARINDEX(' ', @Str) > 0
    BEGIN
        SET @Spc_Indx = CHARINDEX(' ', @Str)
        SELECT @part1 = SUBSTRING(@str, 0, @Spc_Indx),
               @part2 = SUBSTRING(@Str, @Spc_Indx + 1, LEN(@str) -@Spc_Indx)
    END

    IF @part = 1
        SET @retVal = CAST(SUBSTRING(@part1, 0, CHARINDEX('/', @part1)) AS INT)

    IF @part = 2
        SET @retVal = CAST(SUBSTRING(@part2, 0, CHARINDEX('/', @part2)) AS INT)

    RETURN @retVal
END

and you should to use this in your query :

SELECT return_id(AbsenceLink,1),return_id(AbsenceLink,2) FROM table_name

score 0 · Accepted Answer · answered Sep 03 '14 at 14:20

With help from Jaugar Chang I found out the solution to my problem. The code below will show the following result:

______________________________
| Description           | AbsenceLinks  |
|- - - - - - - - - - - - - - + - - - - - - - - - - - - |
| Education               | 19                      |
|- - - - - - - - - - - - - - + - - - - - - - - - - - - |
| Education               | 18                      |
|- - - - - - - - - - - - - - + - - - - - - - - - - - - |
| Holiday                   | 8                        |
|- - - - - - - - - - - - - - + - - - - - - - - - - - - |
| Illness                     | 14                      |
|- - - - - - - - - - - - - - + - - - - - - - - - - - - |
| Illness                     | 15                      |
|- - - - - - - - - - - - - - + - - - - - - - - - - - - |
| Leave of Absence | 20                      |
|______________________________|

WITH links AS
(

SELECT 
    Description,
    SUBSTRING(AbsenceLink,0,CHARINDEX('/',AbsenceLink, 0)) AS AbsenceID,
    CASE 
        WHEN CHARINDEX(' ',AbsenceLink, 0) > 0 
        THEN SUBSTRING(AbsenceLink,CHARINDEX(' ',AbsenceLink, 0)+1,255) 
        ELSE '' 
        END AS linkAbsNr
FROM TableA

UNION ALL

SELECT 
       Description,
       SUBSTRING(linkAbsNr,0,CHARINDEX('/',linkAbsNr, 0)) AS AbsenceID,
       CASE 
           WHEN CHARINDEX(' ',linkAbsNr, 0) > 0 
           THEN SUBSTRING(linkAbsNr,CHARINDEX(' ',linkAbsNr, 0)+1,255)
       ELSE '' 
       END AS RightAbsNr
FROM links
where linkAbsNr <> ''
)

SELECT  Description, AbsenceID 
FROM links
ORDER BY Description

Did you try the non-recursion way? It may speeding up your performance a lot. Worth to try it with your real data. — Jaugar Chang, Sep 03 '14 at 16:05

How to extract and split IDs from nvarchar column surrounded by spaces and a slash to compare to other table's value

The problem is:

4 Answers4

UPDATE: Method with substring and join only.

Method with CTE recursive query