Professional Web Applications Themes

Create default help - Microsoft SQL / MS SQL Server

I have a table as follows Create table T1 ( Col1 varchar(10), Col2 numeric(18,1)) insert T1 ( Col1) values 'ABC123.4' insert T1 ( Col1) values 'NAD' insert T1 ( Col1) values 'XYZ' insert T1 ( Col1) values 'ABC223.4' insert T1 ( Col1) values 'ABC023.4' What i want the output of T1 to look is as follows Col1 Col2 ABC123.4 123.4 NAD null XYZ null ABC223.9 223.9 ABC023.5 23.5 Basically whenever i do an insert I want col2 to be updated with the following logic Case when Col1 not like 'ABC%' then null else cast(substring(col1,4,5) as numeric(18,1)) end Can i create ...

  1. #1

    Default Create default help

    I have a table as follows

    Create table T1
    ( Col1 varchar(10),
    Col2 numeric(18,1))

    insert T1 ( Col1) values 'ABC123.4'
    insert T1 ( Col1) values 'NAD'
    insert T1 ( Col1) values 'XYZ'
    insert T1 ( Col1) values 'ABC223.4'
    insert T1 ( Col1) values 'ABC023.4'


    What i want the output of T1 to look is as follows

    Col1 Col2
    ABC123.4 123.4
    NAD null
    XYZ null
    ABC223.9 223.9
    ABC023.5 23.5

    Basically whenever i do an insert I want col2 to be updated with the
    following logic

    Case when Col1 not like 'ABC%' then null else cast(substring(col1,4,5) as
    numeric(18,1)) end

    Can i create a default with this and bind it to Col2 ? Or does it have to be
    a trigger ? Please let me know how to write the TSQL

    Thank you



    Hassan Guest

  2. #2

    Default Re: Create default help

    Please use computed column as shown

    Create table T1
    ( Col1 varchar(10),
    Col2 as Case when Col1 not like 'ABC%' then null else
    cast(substring(col1,4,5) as
    numeric(18,1)) end)

    HTH,
    Srinivas Sampangi


    "Hassan" <com> wrote in message
    news:phx.gbl... 
    be 


    sampangi Guest

  3. #3

    Default Re: Create default help

    if you want to update the table...

    --whole table
    update T1
    set col2=right(col1,len(col1)+1-nullif(patindex('%[0-9]%',col1),0))

    --selective rows
    update T1
    set col2=right(col1,len(col1)+1-patindex('%[0-9]%',col1))
    where patindex('%[0-9]%',col1)>0

    instead you could create a computed column...

    Create table T1
    ( Col1 varchar(10),
    Col2 as case when patindex('%[0-9]%',col1)>0 then
    right(col1,len(col1)+1-patindex('%[0-9]%',col1))
    else null
    end
    )

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net



    "Hassan" <com> wrote in message
    news:phx.gbl... 
    be 


    oj Guest

Similar Threads

  1. Create PDF from Web page size default to A4?
    By Connor_P_Moran@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 1
    Last Post: June 23rd, 01:31 AM
  2. CREATE TABLE default data question
    By Sanders in forum MySQL
    Replies: 26
    Last Post: January 29th, 09:17 PM
  3. Replies: 4
    Last Post: April 9th, 05:53 PM
  4. Replies: 3
    Last Post: December 4th, 07:35 PM
  5. Create a web site, not default web site
    By mike in forum ASP.NET General
    Replies: 2
    Last Post: August 19th, 11:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139