LEN function in SQL

[#1]

Hi Guys,

Can anyone think of a reason why the Len function doesn't work in SQL I'm using LEN(fieldname) just keeps telling me the function does not exist 😕

[awhitfield]

An Example of the syntax to use is as follows:

Copy

SELECT LEN(FirstName) AS Length, FirstName, LastName 
FROM Sales.vIndividualCustomer
WHERE CountryRegionName = 'Australia';
GO

[awhitfield]

What's your actual select query?

[peter_turner]

Hello Sarah,

That should work ok (works in my SQL 2008, and i think its the same in 2012)

For example:

Your_QV_Table:

Load

LenOfField,

Other_Fields

;

SQL SELECT

len(YourField) as LenOfField,

Other_Fields

From YourTable;

You can always make your query in the SQL Management Console to test it first, and then copy/paste into QV script.

[buzzy996]

in sql server --LEN (Transact-SQL)

in qlikview ---

len( s ) Length of string s. The result is an integer.

Example:

len( Name ) where Name = 'Peter' returns 5.

check ur datatype.

[#6]

thankyou for helping the query is this -

SQL SELECT
  
   tmp_oap.orderatomid as service_id,
  
  
   if(tmp_ap.flagged_parameter=1,orderatomparam_displayval(tmp_oap.orderatomparamid),'') as parameter_flag1
  
//   if(tmp_ap.flagged_parameter =1, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as   parameter_flag1,
//   if(tmp_ap.flagged_parameter =2, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as   parameter_flag2,
//   if(tmp_ap.flagged_parameter =3, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as   parameter_flag3,
//   if(tmp_ap.flagged_parameter =4, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as   parameter_flag4,
//  
//   if(tmp_ap.flagged_parameter =5, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as   parameter_flag5,
//  
//   if(tmp_ap.flagged_parameter =6, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as   parameter_flag6,
//        if(tmp_ap.flagged_parameter =7, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as   parameter_flag7
//          

        FROM
            orderatomparams tmp_oap
            INNER JOIN atomparams tmp_ap  ON (tmp_ap.atomparamid = tmp_oap.atomparamid)
            INNER JOIN orderatoms oa ON (tmp_oap.orderatomid = oa.orderatomid)         
        WHERE
            tmp_ap.flagged_parameter>='1'
           AND  tmp_oap.orderatomid = oa.orderatomid
           AND oa.daterecognised > '01/03/2015'
           AND NOT orderatomparam_displayval(tmp_oap.orderatomparamid)IS NULL
          
GROUP by tmp_oap.orderatomid, tmp_ap.flagged_parameter,tmp_oap.orderatomparamid
;

I was looking to have a table like this

Serviceid , Parameter 1 , Parameter 2 Parameter 3

ID1             (NULL VALUES)

ID1            Address

For some reason for each parameter it was giving me an extra line for null values do you know how to get around this ?

Thanks

Sarah

[#7]

[buzzy996]

can u comment this line and try,

AND NOT orderatomparam_displayval(tmp_oap.orderatomparamid)IS NULL

[#9]

Heres an example of the error message im getting when I use len I haven't come across this one before 😕

[PrashantSangle]

Hi,

Add below in where clause

1=1

Try below

SQL SELECT
 
  tmp_oap.orderatomid as service_id,
 
 
  if(tmp_ap.flagged_parameter=1,orderatomparam_displayval(tmp_oap.orderatomparamid),'') as parameter_flag1
 
//  if(tmp_ap.flagged_parameter =1, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as  parameter_flag1,
//  if(tmp_ap.flagged_parameter =2, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as  parameter_flag2,
//  if(tmp_ap.flagged_parameter =3, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as  parameter_flag3,
//  if(tmp_ap.flagged_parameter =4, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as  parameter_flag4,
// 
//  if(tmp_ap.flagged_parameter =5, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as  parameter_flag5,
// 
//  if(tmp_ap.flagged_parameter =6, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as  parameter_flag6,
//        if(tmp_ap.flagged_parameter =7, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as  parameter_flag7
//        

        FROM
            orderatomparams tmp_oap
            INNER JOIN atomparams tmp_ap  ON (tmp_ap.atomparamid = tmp_oap.atomparamid)
            INNER JOIN orderatoms oa ON (tmp_oap.orderatomid = oa.orderatomid)         
        WHERE
            tmp_ap.flagged_parameter>='1'
          AND  tmp_oap.orderatomid = oa.orderatomid
          AND oa.daterecognised > '01/03/2015'
          AND NOT orderatomparam_displayval(tmp_oap.orderatomparamid)IS NULL

AND 1=1
          
GROUP by tmp_oap.orderatomid, tmp_ap.flagged_parameter,tmp_oap.orderatomparamid
;

Regards,