Professional Web Applications Themes

Update Question - Microsoft SQL / MS SQL Server

Well, here is my suggestion to the problem description you wrote, however the example you included does not follow the description (ID=4 line..) select ID , FirstName , LastName ,'6'+UPPER(SUBSTRING(LastName,1,4)+SUBSTRING (FirstName,1,2)) CustCode from customer Please advise... Edgardo Valdez MCSD, MCDBA, MCSE, MCP+I http://www.edgardovaldez.us/   the  someone ...

  1. #1

    Default Update Question

    Well, here is my suggestion to the problem description you
    wrote, however the example you included does not follow
    the description (ID=4 line..)

    select ID
    , FirstName
    , LastName
    ,'6'+UPPER(SUBSTRING(LastName,1,4)+SUBSTRING
    (FirstName,1,2)) CustCode
    from customer

    Please advise...

    Edgardo Valdez
    MCSD, MCDBA, MCSE, MCP+I
    http://www.edgardovaldez.us/
     
    the 
    someone 
    Edgardo Guest

  2. #2

    Default Update Question

    First, you should mention to the people who did come up
    with this convention that it does not guarantee a unique
    code.

    As for the real question, look at the SQL Substring()
    function and concatenating strings.
    ('6' + Substring(FirstName,...) + Substring(LastName,...) )


    Hope this helps.

    Bob
     
    the 
    someone 
    Bob Guest

  3. #3

    Default Re: Update Question

    See following example:

    create table customer1(
    ID int,
    LastName varchar(50),
    FirstName varchar(50),
    CustCode char(7),
    custcode1 char(7)
    )
    go
    insert into customer1
    select 1 ,'Bean', 'Thomas', '6THOBEA', null
    union all
    select 2 ,'Bear', 'Thomas', '6THOBEA', null
    union all
    select 3 ,'Dive' ,'Jerry' ,'6JERDIV', null
    union all
    select 4 ,'Diver' ,'Jerry' ,'6JERDIV', null
    go
    --required t-sql code

    declare id int, cnt int
    declare f varchar(50), l varchar(50)
    declare old_f varchar(50), old_l varchar(50)
    select old_f = '' , old_l =''
    declare c1 cursor for
    select id, firstname,lastname from customer1 order by lastname,firstname

    open c1

    fetch c1 into id,f,l

    while fetch_status = 0
    begin

    if (f <> old_f and l <> old_l)
    set cnt = 2
    Else
    set cnt = cnt + 1

    select old_f = f , old_l =l

    update customer1 set custcode1 = upper('6' + left (left(firstname, cnt) +
    left(lastname, 4), 6))where id = id

    fetch c1 into id,f,l
    end
    close c1
    deallocate c1
    go

    select * from customer1

    --
    -Vishal
    "Paulo" <com> wrote in message
    news:0ca301c36832$df2519f0$gbl... 


    Vishal Guest

  4. #4

    Default Re: Update Question

    That is awesome. Thank you so much. You are the man.

     
    1 ,'Bean', 'Thomas', '6THOB
    EA', null 
    2 ,'Bear', 'Thomas', '6THOB
    EA', null 
    3 ,'Dive' ,'Jerry' ,'6JERD
    IV', null 
    4 ,'Diver' ,'Jerry' ,'6JERD
    IV', null 
    lastname,firstname 
    (firstname, cnt) + [/ref]
    the [/ref]
    someone [/ref]
    all. 
    Paulo Guest

Similar Threads

  1. Multi-line update question
    By Jim in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 31st, 09:29 PM
  2. a SELECT FOR UPDATE question
    By Tim in forum PostgreSQL / PGSQL
    Replies: 10
    Last Post: February 10th, 03:30 PM
  3. Newbie question - Trigger on Update
    By Nathan in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 7th, 09:17 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