Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2006
    Posts
    12

    Unanswered: substring question

    I am currently working on a way to change telephone numbers
    i am only working off of 1 table, so there should be no link issues

    and trying to setup a script that will search the 'room' table
    and the field 'telephone'
    it is currently set out like this with out the dashes
    dashes are only there for formatting purpose in the post

    123-456-1001

    i was thinking maybe there is a way i can use the substring function
    to look at the last 4 didgets, if it is between a certain set of numbers
    say the last set is 1000-1999 i want the 456 change to a new number say 555
    is there a way i can do it with substring
    or do i need to look at a different way of doing it?

  2. #2
    Join Date
    Apr 2006
    Posts
    12
    sorry for the double post
    but this is the code im looking at
    now i just dont know how to update it to where
    if the last 4 didgets fall between a set
    then change the first 3

    SELECT substring(telephone ,4,3),SUBSTRING(telephone ,7,4)
    From room r
    where
    (my conditions here)

  3. #3
    Join Date
    Apr 2006
    Posts
    12
    latest development
    see below...
    still having problem trying to get this to work

    SELECT
    substring(telephone ,4,3)as first_three, SUBSTRING(telephone ,7,4) as last_four, building_code, room_number

    FROM
    room r

    where
    (other conditions)
    last_four in ('999', '1200')

    now its telling me "Invalid column name 'last_four'. "

  4. #4
    Join Date
    Jun 2006
    Posts
    16
    Have you tried case, case also support functions, so update with case.
    Remember to put the same case statement on the where clause also

  5. #5
    Join Date
    Apr 2006
    Posts
    12
    can you give me a hint as to how it goes
    or possible an example i work off of ?

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Use substring in your where clause
    Code:
    select c1=
    '1234561001' into #t1 union all select
    '1234561502' union all select
    '1234561999' union all select
    '1234562001' union all select
    '1234562501' union all select
    '1234562999' 
    
    update #t1 set c1=substring(c1,1,3)+'555'+substring(c1,7,4)
    where substring(c1,7,4) between '1001' and '1999'
    
    update #t1 set c1=substring(c1,1,3)+'999'+substring(c1,7,4)
    where substring(c1,7,4) in ('1502','2501')
    
    select * from #t1

Posting Permissions

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