How to update recored using Function ???? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 update recored using Function ????

Author

Topic

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-10-19 : 04:09:27

Hello all;
i have written a function where i can trim the first character and last character of a Name and reverse it (ex : mohan result came 'Aho')

my function name [dbo].[ufn_TrimmingCharacters]

now i need to update the table with function

update mohanfreinds set FreindName = [dbo].[ufn_TrimmingCharacters] where FreindsID = 1

how to update using scalar function ????

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-19 : 04:25:46

Hi,

UPDATE <tablename>
SET Column = ScalarFunction(paramValues)
WHERE <Condition>

--
Chandu

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-10-19 : 04:26:54

i found the solution i did a modification in my function passed @ID and wrote select statement in scalar function
select * from mohanfreinds
Declare @ID int
set @ID = 1
update mohanfreinds set FreindName = [dbo].[ufn_TrimmingCharacters](@ID) where FreindsID = @ID

now it is working for single id update but i need to update table in bulk ...suggest me the solution

P.V.P.MOhan

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-10-19 : 04:32:50

i need to update all the records in one go .....please suggest me

P.V.P.MOhan

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-10-19 : 04:33:38

Hi

ALTER FUNCTION UFN_TRIMMINGCHARACTERS (@NAME NVARCHAR(200)) RETURNS NVARCHAR(200)
BEGIN
SET @NAME = RTRIM(LTRIM(@NAME))
SET @NAME = LEFT(@NAME, LEN(@NAME)-1)
SET @NAME = RIGHT(@NAME, LEN(@NAME)-1)
SET @NAME = REVERSE(@NAME)

RETURN @NAME
END
GO
SELECT dbo.UFN_TRIMMINGCHARACTERS('TEST') AS TT

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-19 : 04:53:50

quote:
Originally posted by mohan123

i need to update all the records in one go .....please suggest me

P.V.P.MOhan

To update all rows of a table,

UPDATE YourTable
SET Column = REVERSE(RIGHT(LEFT(Column, len(Column)-1), len(Column)-2))

--
Chandu

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-10-19 : 06:36:56

THanks man it worked like a charm...but now i want to reverse SSN number with out trimming any integer like '123456789' as 987654321

P.V.P.MOhan

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-10-19 : 07:07:50

got man select reverse(23873893)output comes

P.V.P.MOhan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources