converting the varchar value '%,' to data type int

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 2005 Forums

Transact-SQL (2005)

converting the varchar value '%,' to data type int

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2008-12-20 : 05:35:38

Hi,

How can I able to convert

declare @id varchar(500)

set @id = '1,2,3,4,5,6,7,8,9'

SELECT * FROM dbo.Junction where ','+ @id +',' LIKE '%,'+ Junction.StationID +',%'

Thanks.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-12-20 : 07:20:06

What are you trying to match to what here?

--
Gail Shaw
SQL Server MVP

baburk
Posting Yak Master

108 Posts

Posted - 2008-12-20 : 08:22:04

quote:
Originally posted by GilaMonster

What are you trying to match to what here?

--
Gail Shaw
SQL Server MVP

Here junction.stationID contains the stationID I am passing the stationID by concatenating in the asp.net to the procedure.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-20 : 10:23:04

[code]CREATE FUNCTION ParseValues
(@String varchar(8000)
)
RETURNS @RESULTS TABLE
(ID int identity(1,1),
Val int
)
AS
BEGIN
DECLARE @Value varchar(100)

WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN CHARINDEX(',',@String) >0 THEN LEFT(@String,CHARINDEX(',',@String)-1) ELSE @String END,
@String=CASE WHEN CHARINDEX(',',@String) >0 THEN SUBSTRING(@String,CHARINDEX(',',@String)+1,LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END

declare @id varchar(500)

set @id = '1,2,3,4,5,6,7,8,9'

SELECT *
FROM dbo.Junction j
join dbo.ParseValues(@id) pv
on pv.Val= j.StationID
[/code]

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-12-20 : 13:05:39

quote:
Originally posted by baburk

Hi,

How can I able to convert

declare @id varchar(500)

set @id = '1,2,3,4,5,6,7,8,9'

SELECT * FROM dbo.Junction where ','+ @id +',' LIKE '%,'+ Junction.StationID +',%'

Thanks.

Hi,
Use it this way -

SELECT * FROM dbo.Junction where ','+ @id +',' LIKE '%,'+ CAST(Junction.StationID AS VARCHAR(20))+',%'

zeus2026
Starting Member

11 Posts

Posted - 2009-04-04 : 19:51:17

Hi, Im new here and im only a beginner in using sql.. may i know on how to convert carchar value into data type int?? example in this code..

SELECT EmployeeName, DepartmentName, TaxIDNumber
FROM viewEmployee
WHERE (DepartmentName LIKE 'QUA%') AND (TaxIDNumber <= 0)
ORDER BY EmployeeName

I'd like to see all the employee where the value of taxid is null but I dont know the code of it..

Thanks

z3us

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-05 : 16:21:36

SELECT EmployeeName, DepartmentName, TaxIDNumber
FROM viewEmployee
WHERE TaxIDNumber IS NULL
ORDER BY EmployeeName

zeus2026
Starting Member

11 Posts

Posted - 2009-04-05 : 20:09:35

@vijay tnx sir..

z3us

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-05 : 20:53:25

np..in future...open a separate thread for your questions...do not resurrect old threads.

zeus2026
Starting Member

11 Posts

Posted - 2009-04-06 : 00:40:13

hi, please help..

SELECT EmployeeName, CONVERT(varchar, DateHired, 101) AS Date
FROM viewEmployee
WHERE (DateHired >= '5/01/08') AND (Separated = 0) AND (DeptID < 39)
ORDER BY DateHired

in this code i want to view all the employee where 5 months contract has been done in the months of April, 2009 but I should view the table with no specific date.. like this

EmployeeName DateHired
Marlon 11-2008

please kindly help me huhuh. Thanks

z3us

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-06 : 05:39:44

quote:
Originally posted by zeus2026

hi, please help..

SELECT EmployeeName, CONVERT(varchar, DateHired, 101) AS Date
FROM viewEmployee
WHERE (DateHired >= '5/01/08') AND (Separated = 0) AND (DeptID < 39)
ORDER BY DateHired

in this code i want to view all the employee where 5 months contract has been done in the months of April, 2009 but I should view the table with no specific date.. like this

EmployeeName DateHired
Marlon 11-2008

please kindly help me huhuh. Thanks

z3us

Did you see the previous reply?

Open this as new topic

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources