How to covert nvarchar to int - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

How to covert nvarchar to int

Author

Topic

sql_developer
Starting Member

5 Posts

Posted - 2008-12-10 : 05:12:31

hi,

I have got two Tables TableA and TableB

TableA has fields: SessionID(int) and CatIDs(nvarchar)
TableB has fields: CatID(int) and Name(char)

And i want to fetch Name field in TableB which corresponds to Session ID in TableA, for that reason i am making relationship between TableA.CatIDs and TableB.CatID althought both are different dataype but this is only option in database to fetch data.

Other thing to notice when i run query:

Select
TableA.SessionID,
TableB.Name
From
TableA, TableB
Where
TableA.CatIDs = TableB.CatID

it gives me error:
Syntax error converting the nvarchar value '10;11' to a column of data type int.

Where TableA.CatIDs has got int data seperated by ; somthing like :

8;10
10;11
12;8;15;2

I dont mind if we trancate every thing after 1st integer value, so we need somthing like

8
10
12

Looking forward for reply.
Many thanks in advance

chrianth
Yak Posting Veteran

50 Posts

Posted - 2008-12-10 : 05:36:39

try this...

Select
TableA.SessionID,
TableB.Name
From
TableA, TableB
Where isnumeric(SUBSTRING(TableA.CatIDs,1,CHARINDEX(';',TableA.CatIDs))) = 1 and
cast((SUBSTRING(TableA.CatIDs,1,CHARINDEX(';',TableA.CatIDs))) as int) = TableB.CatID

there can be more better approaches than this. HTH :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-10 : 05:42:12

[code]Select TableA.SessionID,
TableB.Name
From TableB
inner join TableA on ';' + TableA.CatIDs + ';' LIKE '%;' + cast(TableB.CatID as nvarchar(11)) + ';%'[/code]

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

khasim76
Starting Member

35 Posts

Posted - 2008-12-10 : 06:09:01

can you guys please explain in brief in words what you have coded i am trying to learn industry specific sql queries

regards

khasim Shaik

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-10 : 06:18:24

Don't learn these techniques.
Learn how to normalize your database instead.

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

sql_developer
Starting Member

5 Posts

Posted - 2008-12-10 : 06:22:03

Thanks a lot both of you!

Peso's Solution worked like charm. cheers peso.

If chrianth solution's could have worked then i can do some more integeration on that query.

I have added one more table TableC because i need date from that table and it has got sessionID aswell so i have joined that to TableA.SessionID.

so query is now:

Select
TableB.Name,
TableC.SessionID
From
Category Inner Join
TableA On ';' + TableA.CatIDs + ';' Like '%;' +
Cast(TableB.CatID As NVarChar(11)) + ';%' Inner Join
TableC On TableC.SessionID = TableA.SessionID
Order By
TableC.SessionID

I just want to categorize TableB.Name according to TableC.SessionID.
Like

TableB.Name TableC.SessionID
ABC 34
DEF 5

so its basically counting number of TableC.SessionID. i am sure we need to add count function, i have tried that but it is not producing any reslut so there could be somthing missing in my query.

Any help highly appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-10 : 06:30:27

[code]SELECT cat.Name AS theName,
COUNT(c.SessionID) AS theCuunt
FROM Category AS cat
INNER JOIN TableA AS a ON ';' + a.CatIDs + ';' LIKE '%;' + CAST(cat.CatID AS NVARCHAR(11)) + ';%'
INNER JOIN TableC AS c ON c.SessionID = a.SessionID
GROUP BY cat.Name[/code]

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

sql_developer
Starting Member

5 Posts

Posted - 2008-12-10 : 06:52:41

HI Peso;

Thank you so much for very quick reply.

i have just messed up last query by actulay adding real name of table so it confused you i think. sorry about that.
i am sending you query with all real table names:

Select
Category.Name,
sessioninfo.CreateTime,
SessionInfo.SessionID as Total
From
Category Inner Join
SessionSummaryEx On ';' + SessionSummaryEx.CatIDs + ';' Like '%;' +
Cast(Category.CatID As NVarChar(11)) + ';%' Inner Join
SessionInfo On SessionInfo.SessionID = SessionSummaryEx.SessionID And
sessionInfo.CreateTime >= ? and sessioninfo.CreateTime <= ?

Order By
SessionInfo.SessionID

where createtime is date field.

MANY THANKS....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-10 : 06:57:35

I can't tell what you want, but if you want to count something, see my previous response.

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

sql_developer
Starting Member

5 Posts

Posted - 2008-12-10 : 07:02:28

i just want to count sessioninfo.sessionid in last query thas it. Ignore my previous complication.

Cheers

sql_developer
Starting Member

5 Posts

Posted - 2008-12-10 : 08:24:31