Post reply

Insert text in a text field

  • July 12, 2014 at 2:54 am

    #309066

    Hi, i would appreciate suggest the SQL sentence, thanks

    CREATE TABLE [dbo].[instructions](

    [site_no] [int] NOT NULL,

    [instructions] [text] NULL

    )

    Select top 3 * from instructions

    Output

    Site_noInstructions

    20Request PIN then proceed

    21Request PIN if wrong request name

    22Request PIN allowed to use only numbers

    All text instructions start with “Request PIN” but after that the text are different for every site_no

    I need insert in all site_no rows and after the “Request PIN” the text “and codeword” keeping the current rest of text

    Desired output

    Site_noInstructions

    20Request PIN and codeword then proceed

    21Request PIN and codeword if wrong request name

    22Request PIN and codeword allowed to use only numbers

    Can you please suggest the right update SQL sentence? Beforehand thank you

  • July 12, 2014 at 3:06 am

    #1728924

    First a quick question, why use [text] data type? Better change that to a (n)varchar(max)!

    Below is a quick example of the replace function (on varchar(max)), does not work on text.

    😎

    USE tempdb;

    GO

    CREATE TABLE [dbo].[instructions](

    [site_no] [int] NOT NULL,

    [instructions] [text] NULL

    );

    INSERT INTO dbo.instructions (site_no,instructions)

    VALUES

    (20,'Request PIN then proceed')

    ,(21,'Request PIN if wrong request name')

    ,(22,'Request PIN allowed to use only numbers');

    SELECT

    I.site_no

    ,REPLACE(CAST(I.instructions AS VARCHAR(MAX)),'Request PIN','Request PIN and codeword')

    FROM dbo.instructions I

    DROP TABLE dbo.instructions;

    Results

    site_no instructions

    ----------- ----------------------------------------------------

    20 Request PIN and codeword then proceed

    21 Request PIN and codeword if wrong request name

    22 Request PIN and codeword allowed to use only numbers

  • July 12, 2014 at 3:09 am

    #1728925

    Thanks a lot Eirikur 🙂

  • July 12, 2014 at 3:18 am

    #1728926

    Claudio Pinto (7/12/2014)

    Thanks a lot Eirikur 🙂

    You are welcome Claudio;-)

    😎

    You should anyway look into replacing the text data type, as according to MSDN "ntext , text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead."

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

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