append the records in the field

  • 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

  • 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

     

     

     

  • 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)


    Regards,

    Coach James

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply