JimmyJimm JimmyJimm - 3 months ago 8
SQL Question

Like statment read from result table to compare

In my database there are articles. Example or articles below, as you see there is always main article (with .1 at the end) which could contain combinations (.+1) for next

1st article:
-------------------------------------------------------
12.3356.1 <- main artikel (1)
12.3356.2 <- sub artikel (2)
12.3356.3 <- sub artikel (3)

2nd article:
-------------------------------------------------------
4.641.1 <- main artikel (1)

3rd article:
-------------------------------------------------------
664.6241.1 <- main artikel (1)
664.6241.2 <- sub artikel (2)
664.6241.3 <- sub artikel (3)
664.6241.4 <- sub artikel (4)

4th article:
-------------------------------------------------------
7.31.1 <- main artikel (1)
7.31.2 <- sub artikel (2)


Now somwhere in my program i get some specific articles. Let's say:

12.3356.3
7.31.2


Now what i have to do is to take them and all rest associated within same number so in this case result would be as follows:

12.3356.1
12.3356.2
12.3356.3

7.31.1
7.31.2


so what i did so far is this:

;WITH cutted_nummers (cut_num) as (select REVERSE(SUBSTRING(REVERSE(Nummer),CHARINDEX('.',REVERSE(Nummer)),len(Nummer))) from T_Artikel),
dist_nums (dist_num) as (select distinct(cut_num) from cutted_nummers),
final (nums) as (select dist_num from dist_nums) select * from final


so by this query i get those articles, then i am cutting last .digit out of those two articles so i got in final table:

12.3356. (without 3 at the end)
7.31. (without 2 at the end)


Now having this result i have to look to my artikel table and get all nummbers which start with one of those two and that's something i don't know how to do. I think i need like statment but how to say to LIKE from this result so this should look like:

select * from artikeltable that nummerfield is like '12.3356.%' or '7.31.%'


so like has to be constructed (in this case) based on two articles from 'final' table.

EDIT for NEED:

;WITH cutted_nummers (cut_num) as (select REVERSE(SUBSTRING(REVERSE(Nummer),CHARINDEX('.',REVERSE(Nummer)),len(Nummer))) from T_Artikel),
dist_nums (dist_num) as (select distinct(cut_num) from cutted_nummers),
final (nums) as (select dist_num from dist_nums)
SELECT Nummer FROM T_Artikel
WHERE
SUBSTRING(Nummer , 0, LEN(Nummer ) - CHARINDEX(REVERSE(Nummer ), '.', 0)) IN
(
SELECT nums FROM final
)

Answer

You need dynamic query.

DECLARE @Tmp NVARCHAR(MAX) = ''
SELECT @Tmp = COALESCE(@Tmp, '') +  ' nummerfield LIKE ''' + ColumnName + '%'' OR' FROM final 

DECLARE @Query NVARCHAR(MAX)  = 'select * from artikeltable  WHERE ' + LEFT(@Tmp, LEN(@Tmp) - 3)
--SELECT @Query
EXEC sp_executesql @Query

By the way, you can find this way too.

DECLARE @val NVARCHAR(100) = '12.3356.2'
SELECT SUBSTRING(@val, 0, LEN(@val) + 2 -  CHARINDEX('.', REVERSE(@val))) -- 12.3356.

Update

Try this then

SELECT * FROM artikeltable
WHERE
    SUBSTRING(nummerfield , 0, LEN(nummerfield ) + 2 -  CHARINDEX('.', REVERSE(@val))) IN 
    (
        SELECT * FROM final
    )