Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2004
    Posts
    21

    Cool Unanswered: How many characters has my field?

    Does anyone know a function in SQL or how I can get the amount of characters of a field?

    I have a column named NU_IPS wich contains data varchar type, that has a % symbol at the end, like 9.7% and so on... But in original table it can't be like this (it has to bem float type), I just want the number content, like this 9.7 For that I need in DTS put a query that convert it. That's why I need a function or something that can get the quantity of characters of each field.

    So, It would be someting like this...

    select substring(convert(varchar(getSizeField() - 1), nu_ipi), 1, 4) from dbo.t_STAGEAREACHAIR

    It would cut always the last caracter, wich is '%'...

    Any clues?
    All posts are welcome, thanks.

  2. #2
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74

    Look up the Len() - function ...

    Look up the Len() - function ...
    The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents.

  3. #3
    Join Date
    Dec 2004
    Posts
    21
    Thanks Nephilim!

    But why can't I put this function in the convert if it returns a int type?
    I guess it can't process each field at time, 'cause we can admit there's a table in convert varchar paremeter, it's obvious.

    Like:
    select substring(convert(varchar(SELECT len(nu_ipi) - 1 FROM dbo.t_STAGEAREACHAIR), nu_ipi), 1, 4) from dbo.t_STAGEAREACHAIR

    I've tried putting an IF statment...

    Like:
    if ((select len(nu_ipi) - 1 from dbo.t_STAGEAREACHAIR) = 1)
    begin
    select substring(convert(varchar(1), nu_ipi), 1, 4) from dbo.t_STAGEAREACHAIR
    end

    But it returns more than 1 value though.

    Have you an idea how I can do it?
    Thanks

  4. #4
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74
    First of all, there really is no need to put a subselect in the select you posted :

    select substring(convert(varchar(SELECT len(nu_ipi) - 1 FROM dbo.t_STAGEAREACHAIR), nu_ipi), 1, 4) from dbo.t_STAGEAREACHAIR

    I think you are looking for something like this

    select substring(convert(Varchar(10), NU_IPS), 1, len(convert(Varchar(10), NU_IPS)) -1 from dbo.t_STAGEAREACHAIR

    however I'm not exactly certain I've understood your problem fully....
    The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    maybe i don't understand it fully either, but NU-IPS is already a varchar, so...
    Code:
    select left(NU_IPS,len(NU_IPS)-1) ...
    or alternatively
    Code:
    select replace(NU_IPS,'%','') ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2004
    Posts
    21
    r937 and Nephilim
    I think You got it, that's what I wanted.
    It works...
    Really thanks!
    Juliane

Posting Permissions

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