Professional Web Applications Themes

striping char from field - Microsoft SQL / MS SQL Server

SELECT SUBSTRING('com', CHARINDEX('', 'com')+1, LEN('com')) Against a column in a table: SELECT SUBSTRING(column_name, CHARINDEX('', column_name)+1, LEN(column_name)) "chau" <com> wrote in message news:010501c35c4a$2a9307c0$gbl... ...

  1. #1

    Default Re: striping char from field

    SELECT SUBSTRING('com', CHARINDEX('', 'com')+1,
    LEN('com'))

    Against a column in a table:

    SELECT SUBSTRING(column_name, CHARINDEX('', column_name)+1,
    LEN(column_name))




    "chau" <com> wrote in message
    news:010501c35c4a$2a9307c0$gbl... 


    Aaron Guest

  2. #2

    Default Re: striping char from field

    Nice, I might actually be able to answer this question.
    SELECT RIGHT(s,LEN(s)-CHARINDEX('',s))

    Ray at work

    "chau" <com> wrote in message
    news:010501c35c4a$2a9307c0$gbl... 


    Ray Guest

  3. #3

    Default Re: striping char from field

    something like this?


    create table #example (col1 varchar(20))
    insert into #example values ('com')
    insert into #example values ('com')

    SELECT SUBSTRING(col1,
    CHARINDEX('', col1)+1,
    LEN(col1) - CHARINDEX('', col1))
    FROM #example


    --
    Keith, SQL Server MVP

    "chau" <com> wrote in message news:010501c35c4a$2a9307c0$gbl... 
    Keith Guest

  4. #4

    Default Re: striping char from field

    great, modify to 'RIGHT(columnname,LEN(columnname)-
    CHARINDEX('',columnname))' and it work.

    thanks a lot
     
    >
    >
    >.
    >[/ref]
    chau Guest

  5. #5

    Default Re: striping char from field

    You want to datalength() instead of len() if your datatype is char.

    e.g.
    declare t char(20)
    set t='com'
    select right(t,len(t)-charindex('',t)) as [len],
    right(t,datalength(t)-charindex('',t)) as [datalength]

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


    "chau" <com> wrote in message
    news:022e01c35c4e$b5317e30$gbl... 
    > >
    > >
    > >.
    > >[/ref][/ref]


    oj Guest

Similar Threads

  1. #39533 [NEW]: ord() cast with (unsigned char), chr() with (signed char)
    By fred at cashette dot com in forum PHP Bugs
    Replies: 0
    Last Post: November 16th, 02:54 PM
  2. Replies: 5
    Last Post: January 16th, 11:30 AM
  3. Looping through a field and deleting first char
    By Brett in forum Macromedia Director Lingo
    Replies: 5
    Last Post: January 7th, 06:04 PM
  4. Striping Leading Zero's
    By Grant in forum Microsoft Access
    Replies: 6
    Last Post: July 3rd, 09:33 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