CODEWITHSUNDEEP
sql-servert-sql
user6154174
user6154174

Reputation:

How to convert varchar contents(1,2,3) data to integer array in SQL without using Stored Procedure?

I have two tables called tblfilms and tblfilmdetails.

First table tblfilm columns and data.

categoryID  filmCodes
----------  ----------
1           1,2,3
2           4,5,6

Second table tblfilmdetails columns and data.

filmCode  filmName
--------  ----------
1         Kungfu Panda
2         Inside out
3         Karate Kid
4         Terminator
5         Jumanji
6         Batman

I need to get filmName from tblfilmdetails table where categoryID equals 1.

I have tried the below query to achieve it but it shows error

select filmname from tblfilmdetails where filmCode in (select filmCodes from tblfilm where categoryID=1)

The problem is because my filmCodes column is varchar data so that it returns error that it cannot be convert a varchar to int.

How do I achieve this?

I do not want to use Stored Procedure due to some other reason.

Upvotes: 3

Views: 12025

Answers (4)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

First you need to have Split function and the use below Simple query

Split Function

FUNCTION [dbo].[Split](@String nvarchar(4000), @Delimiter char(1))  
    RETURNS @Results TABLE (ITEM nvarchar(4000))  
AS  
BEGIN  
      DECLARE @StartIndex INT, 
      @EndIndex INT
      SET @StartIndex = 1
      IF SUBSTRING(@String, LEN(@String) - 1, LEN(@String)) <> @Delimiter
      BEGIN
            SET @String = @String + @Delimiter
      END

      WHILE CHARINDEX(@Delimiter, @String) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Delimiter, @String)

            INSERT INTO @Results(ITEM)
            SELECT RTRIM(LTRIM(SUBSTRING(@String, @StartIndex, @EndIndex - 1)))

            SET @String = SUBSTRING(@String, @EndIndex + 1, LEN(@String))
      END
    RETURN  
END

SQL Query

SELECT *
FROM tblfilms AS M
    CROSS APPLY dbo.Split(M.filmCodes,',') AS T
    INNER JOIN tblfilmdetails AS G ON G.filmCode = CAST(T.ITEM AS int)
WHERE M.categoryID = 1

Upvotes: 4

DhruvJoshi
DhruvJoshi

Reputation: 17126

For these types of cases you can leverage the string matching in SQL like below query

select * from tblfilms f inner join tblfilmdetails d on 
','+f.filmcodes like '%,'+cast(d.filmcode as varchar)+',%'

Explanation: We add a comma to the string of feecodes while matching so that each filmcode in string starts and ends with comma and match it so on the left side with

'%,'+cast(d.filmcode as varchar)+',%'

which translates to filmcode with comma at start and end anywhere in the string

Working sql demo

Upvotes: 1

Marco Bong
Marco Bong

Reputation: 690

You need to split filmcodes into seperate row.

Try this.

declare @tb as Table (CategoryId int, FilmCodes nvarchar(50))
declare @ProcessdeTb as Table (CategoryId int, FilmCodes nvarchar(50))
insert into @tb select 1,'1,2,3'
union select 2,'4,5,6'

insert into @ProcessdeTb
SELECT CategoryId,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS FilmCodes
FROM
(
SELECT CategoryId,CAST('<XMLRoot><RowData>' + REPLACE(FilmCodes,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM   @tb
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)


select * from @ProcessdeTb

select filmname from tblfilmdetails where filmCode in (select filmCodes from @ProcessdeTb where categoryID=1)

Upvotes: -1

toanpt
toanpt

Reputation: 66

you can convert filmCode from int to varchar by using "CAST". In your example: select filmname from tblfilmdetails where cast(filmCode as varchar) in (select filmCodes from tblfilm where categoryID=1) Hope it work.

Upvotes: 1

Related Questions