April 27, 2004 at 5:27 am
i want to know how to add some text at the starting and at the end in all records in a specific field of a table in sql server database. if possible a stored procedure for this purpose
April 27, 2004 at 6:54 am
Not too sure of your question ?? Is the below what you want ??
SELECT 'Sometext1 ' + au_lname + ' Sometext2'
FROM Authors
UPDATE a
SET a.au_lname = 'Sometext1 ' +a.au_lname + ' Sometext2'
FROM dbo.Authors a
April 28, 2004 at 12:33 am
Use a UDF (User Defined Function) for your CALCULATED COLUMN,
See following example:
/* CODE BETTER, FUNCTION BETTER */
-- Your code should read like it runs
-- FIRST make sure you have an index like
CREATE INDEX <tablename>_001 ON <tablename>(Cus_No,Tel_No);
-- SECOND create UDF(from above examples SP's)
-- to use for calculating your Seq_No
-- for each new record as they are added
CREATE FUNCTION getSeq(@Cus_No integer, @Tel_No integer)
RETURNS integer
AS
BEGIN
DECLARE @Seq_No integer
SELECT @Seq_No = count(*)
FROM <tablename> (NOLOCK INDEX=<tablename>_001)
WHERE Cus_No = @Cus_No
AND Tel_No <= @Tel_No
RETURN @Seq_No
END
-- Ha, now you can use it in your query:
SELECT *, getSeq(Cus_No,Tel_No) FROM <tablename>
-- or use this function as a
-- YES! a computed column to your <tablename>
-- Alter table with a calculated column
ALTER TABLE <tablename>
ADD Seq_No AS getSeq(Cus_No,Tel_No)
Coach James
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply