how to Convert nvarchar to integer

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

how to Convert nvarchar to integer

Author

Topic

carlossiwaily
Starting Member

24 Posts

Posted - 2008-10-25 : 16:51:56

hello,

i have sql q as follow

select newsid,relatedarticles, newsbody from tblnews
where lang= 1 and newsid in (select relatedarticles from tblnews where newsid = @newsid)

the problem is the relatedarticles is nvarchar(50)

error massage i get is : converting the nvarchar value (2,3,4) to a colum of data type int

the relatedarticles contain value as 2,3,4

any help please

carlossiwaily

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-26 : 01:10:55

[code]select n1.newsid,n1.relatedarticles, n1.newsbody
from tblnews n1
inner join tblnews n2
on ','+ n2.relatedarticles + ',' LIKE '%,'+ CAST(n1.newsid AS varchar(10))+ ',%'
where n1.lang= 1 [/code]

carlossiwaily
Starting Member

24 Posts

Posted - 2008-10-27 : 07:01:24

thanks for you reply, quary returns all news articles .. it should returns only the newsid match the realtedarticles. in this filed i have 2,3,4 then the qury should return news for newsid 2,3,4

thanks

carlossiwaily

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 07:18:57

it wont return all. can you show some sample data from your tables?

carlossiwaily
Starting Member

24 Posts

Posted - 2008-10-28 : 10:02:05

hello,

my table

newsid languageid newstitle relatedarticles
1 1 fffffffff
2 1 ddddddddd
3 1 eeeeeeeee 1,2

the relatedarticles in nvarchar(50)

i want a quary to return all record that in the relatedarticles field
given languageid and newsid

select n1.newsid,n1.relatedarticles, n1.newstitle
from tblnews n1
inner join tblnews n2
on ','+ n2.relatedarticles + ',' LIKE '%,'+ CAST(n1.newsid AS varchar(10))+ ',%'
where n1.lang= 1

the above quary return wrong records.
e.g if i pass languageid = 1 and newsid = 3 then result should return record 1,2

carlossiwaily

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 10:23:27

what will value of relatedarticles for records 1 & 2?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-28 : 11:29:11

[code]DECLARE @Sample TABLE
(
newsID INT,
languageID INT,
newsTitle VARCHAR(20),
relatedArticleIDs VARCHAR(20)
)

INSERT @Sample
SELECT 1, 1, 'fffffffff', NULL UNION ALL
SELECT 2, 1, 'ddddddddd', NULL UNION ALL
SELECT 3, 1, 'eeeeeeeee', '1,2'

DECLARE @languageID INT,
@newsID INT

SELECT @languageID = 1,
@newsID = 3

SELECT s1.newsID,
s1.languageID,
s1.newsTitle,
s1.relatedArticleIDs
FROM @Sample AS s1
INNER JOIN @Sample AS s2 ON ',' + s2.relatedArticleIDs + ',' LIKE ',%' + CAST(s1.newsID AS VARCHAR(11)) + '%,'[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 11:35:56

[code]
create proc getdata
@newsid int,
@languageid int
as
select n1.newsid,n1.relatedarticles, n1.newstitle
from tblnews n1
inner join tblnews n2
on ','+ n2.relatedarticles + ',' LIKE '%,'+ CAST(n1.newsid AS varchar(10))+ ',%'
where n2.lang= @languageid
and n2.newsid=@newsid
go

exec getdata 3,1[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-28 : 11:43:53

Ooops! I missed to copy the WHERE part

WHERE		s2.languageID = @languageID
		AND s2.newsID = @newsID

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources