Replace() & upper () in stored procedure

Hi;

I have a stored procedure :

<code
Create Procedure ControlDept
(

@.DeptID nvarchar(10)
)
As
If Exists
(
Select DeptName From Departments Where
DeptID LIKE @.DeptID
)
Return 1
Else
Return 0

Now I want to apply replace and upper functions to DeptID in database before saying
"DeptID LIKE @.DeptID".

for example the parameter is :"D&V"
DeptID in database is:"d & v" //there are spaces

if I say DeptID LIKE @.DeptID nothing is found because of character nonmatching
So I have to apply replace & upper functions to the column DeptID in database

but how?
can you help me please??You probably don't need the "Upper" function since *most* SQL Server functions are case insensitive by default.

As for the spaces, not sure what to tell you there. That's a one-off solution that you will have to code manually. For instance, what happens when the value is "D& V"? There are 2 spaces now, and you'd have to code a check for that too.

Also, if you are using LIKE, you need to have a % character. For example:
DeptID LIKE @.DeptID + '%'

Replace data in SQL server table column

What is the correct syntax to replace a field data nvarchar(50)
Current data = 0020-10-02
Change = 2003-10-02
Thank you in advance.Originally posted by josephjthomas
What is the correct syntax to replace a field data nvarchar(50)

Current data = 0020-10-02
Change = 2003-10-02

Thank you in advance.

Update table_name
set (column_name='2003-10-02)
where (select * from table_name where column_name='0020-10-02')|||Why do you assume it's 2003?

DECLARE @.x varchar(50), @.y varchar(50)
SELECT @.x = '0020-10-02'

SELECT SUBSTRING(@.x,3,2)+'03'+RIGHT(@.x,6)|||It's definitely 2003. The entry was mis-typed.

I tried the Update query but that didn't work. Even adding the missing hypen.

Brett,

If I try what you posted, how does it process the update?|||USE Northwind
GO

CREATE TABLE myTable99(x varchar(50))
GO

INSERT INTO myTable99(x)
SELECT '0020-10-02' UNION ALL
SELECT '2004-10-02'
GO

SELECT * FROM myTable99

UPDATE myTable99
SET x = SUBSTRING(x,3,2)+'03'+RIGHT(x,6)
WHERE x = '0020-10-02'

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO

Or can just had code it in the set|||Originally posted by josephjthomas
It's definitely 2003. The entry was mis-typed.

I tried the Update query but that didn't work. Even adding the missing hypen.

Brett,

If I try what you posted, how does it process the update?
My bad:

UPDATE table_name
SET column_name = '2003-10-02'
WHERE EXISTS
(SELECT *
FROM table_name
WHERE column_name = '0020-10-02')|||That worked great! Thank you!|||Originally posted by Ida Hoe
My bad:

UPDATE table_name
SET column_name = '2003-10-02'
WHERE EXISTS
(SELECT *
FROM table_name
WHERE column_name = '0020-10-02')

Where's the coorelation...

Why not a simple WHERE?

That'll update all rows...I was suprised it ran...

USE Northwind
GO

CREATE TABLE myTable99(x varchar(50))
GO

INSERT INTO myTable99(x)
SELECT '0020-10-02' UNION ALL
SELECT '2004-10-02'
GO

SELECT * FROM myTable99

UPDATE myTable99
SET x = '2003-10-02'
WHERE EXISTS ( SELECT *
FROM myTable99
WHERE x = '0020-10-02')

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||Sorry. I meant to say Brett's script ran and not Ida Hoe.

Thank you both. :-)