Report # of characters for an integer value

• gpeters

  SSC Veteran

  Points: 247

  More actions

  September 10, 2008 at 6:43 am

  #382584

  Hi. I was wondering if anyone would have a SQL query to report # of characters for an integer value. Thank you.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 10, 2008 at 7:00 am

  #869588

  You mean, the length? Like this?

  SELECT IntegerValue, LEN(IntegerValue) AS Length

  FROM (

  SELECT CAST(1 AS INT) AS IntegerValue UNION ALL

  SELECT 22 UNION ALL

  SELECT 333 UNION ALL

  SELECT 4444 ) d

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• gpeters

  SSC Veteran

  Points: 247

  More actions

  September 10, 2008 at 7:08 am

  #869594

  Hi Mr or Mrs. 500. Yes that is correct. For example:

  value = -500.55

  I want to get the result of 7 which includes the - sign and decimal point.

  select

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 10, 2008 at 7:26 am

  #869611

  -500.55 isn't an integer value. Can you be more specific?

  Cheers

  ChrisM

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• gpeters

  SSC Veteran

  Points: 247

  More actions

  September 10, 2008 at 7:38 am

  #869626

  Hi. I am working with a float datatype.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 10, 2008 at 7:56 am

  #869649

  Oh, ok...

  [font="Courier New"]SELECT FloatValue, LEN(FloatValue) AS Length

  FROM (

     SELECT CAST(1 AS FLOAT) AS FloatValue UNION ALL

     SELECT 22 UNION ALL

     SELECT 333 UNION ALL

     SELECT 4444 UNION ALL

     SELECT -500.55

  ) d[/font]

  Note that there is an implicit conversion by the LEN function, which takes a string parameter.

  Results:

  FloatValue Length

  ----------------------------------------------------- -----------

  1.0 1

  22.0 2

  333.0 3

  4444.0 4

  -500.55000000000001 7

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• gpeters

  SSC Veteran

  Points: 247

  More actions

  September 10, 2008 at 8:04 am

  #869659

  I was hoping I could convert the float value to string in order use len in my select stmt:

  i.e. select len ('-500.55') which would return the value of 7

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 10, 2008 at 8:12 am

  #869671

  gpeters (9/10/2008)

  ---

  I was hoping I could convert the float value to string in order use len in my select stmt:

  i.e. select len ('-500.55') which would return the value of 7

  Of course you can...

  [font="Courier New"]SELECT FloatValue,

     CAST(FloatValue AS VARCHAR) AS VARCHARValue,

     LEN(CAST(FloatValue AS VARCHAR)) AS VARCHARValueLength,

     LEN(FloatValue) AS Length

  FROM (

     SELECT CAST(1 AS FLOAT) AS FloatValue UNION ALL

     SELECT 22 UNION ALL

     SELECT 333 UNION ALL

     SELECT 4444 UNION ALL

     SELECT -500.55

  ) d

  [/font]

  Results:

  FloatValue VarcharValue VarcharValueLength Length

  ---------------------------- ------------------------- ------------------ ------

  1.0 1 1 1

  22.0 22 2 2

  333.0 333 3 3

  4444.0 4444 4 4

  -500.55000000000001 -500.55 7 7

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• gpeters

  SSC Veteran

  Points: 247

  More actions

  September 10, 2008 at 8:24 am

  #869690

  Awesome. Thank you Mr or Mrs. 500.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 10, 2008 at 8:27 am

  #869695

  gpeters (9/10/2008)

  ---

  Awesome. Thank you Mr or Mrs. 500.

  You're very welcome 'Forum Newbie', thanks for the feedback.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• gpeters

  SSC Veteran

  Points: 247

  More actions

  September 10, 2008 at 9:30 am

  #869756

  Chris Morris (9/10/2008)

  ---

  gpeters (9/10/2008)

  ---

  I was hoping I could convert the float value to string in order use len in my select stmt:

  i.e. select len ('-500.55') which would return the value of 7

  Of course you can...

  [font="Courier New"]SELECT FloatValue,

     CAST(FloatValue AS VARCHAR) AS VARCHARValue,

     LEN(CAST(FloatValue AS VARCHAR)) AS VARCHARValueLength,

     LEN(FloatValue) AS Length

  FROM (

     SELECT CAST(1 AS FLOAT) AS FloatValue UNION ALL

     SELECT 22 UNION ALL

     SELECT 333 UNION ALL

     SELECT 4444 UNION ALL

     SELECT -500.55

  ) d

  [/font]

  Results:

  FloatValue VarcharValue VarcharValueLength Length

  ---------------------------- ------------------------- ------------------ ------

  1.0 1 1 1

  22.0 22 2 2

  333.0 333 3 3

  4444.0 4444 4 4

  -500.55000000000001 -500.55 7 7

  I the following datatype error when I tried your query earlier:

  Function CHAR_LENGTH invoked with wrong number or type of argument(s).

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 10, 2008 at 9:51 am

  #869774

  There isn't a Function CHAR_LENGTH in my code. Did you copy and paste?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• gpeters

  SSC Veteran

  Points: 247

  More actions

  September 10, 2008 at 9:58 am

  #869781

  Chris Morris (9/10/2008)

  ---

  There isn't a Function CHAR_LENGTH in my code. Did you copy and paste?

  Hi. Yes I executed the exact query you supplied.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 10, 2008 at 10:08 am

  #869789

  gpeters (9/10/2008)

  ---

  Chris Morris (9/10/2008)

  ---

  There isn't a Function CHAR_LENGTH in my code. Did you copy and paste?

  Hi. Yes I executed the exact query you supplied.

  I've just run this again, exactly as it appears here...

  SELECT FloatValue,

  CAST(FloatValue AS VARCHAR) AS VARCHARValue,

  LEN(CAST(FloatValue AS VARCHAR)) AS VARCHARValueLength,

  LEN(FloatValue) AS Length

  FROM (

  SELECT CAST(1 AS FLOAT) AS FloatValue UNION ALL

  SELECT 22 UNION ALL

  SELECT 333 UNION ALL

  SELECT 4444 UNION ALL

  SELECT -500.55

  ) d

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• gpeters

  SSC Veteran

  Points: 247

  More actions

  September 10, 2008 at 10:18 am

  #869796

  Chris Morris (9/10/2008)

  ---

  gpeters (9/10/2008)

  ---

  Chris Morris (9/10/2008)

  ---

  There isn't a Function CHAR_LENGTH in my code. Did you copy and paste?

  Hi. Yes I executed the exact query you supplied.

  I've just run this again, exactly as it appears here...

  SELECT FloatValue,

  CAST(FloatValue AS VARCHAR) AS VARCHARValue,

  LEN(CAST(FloatValue AS VARCHAR)) AS VARCHARValueLength,

  LEN(FloatValue) AS Length

  FROM (

  SELECT CAST(1 AS FLOAT) AS FloatValue UNION ALL

  SELECT 22 UNION ALL

  SELECT 333 UNION ALL

  SELECT 4444 UNION ALL

  SELECT -500.55

  ) d

  Strange. What type of db are you using?