Please explain what this mean: VARCHAR( 25 ) NOT NULL ,

VARCHAR( 25 ) NOT NULL ,

from update database.php

can anyone explain one by one what varchar is and what that # 25 is and etc.

can i start a new database.php with same VARCHAR( 25 ) NOT NULL ?

sql server – Error converting data type varchar to numeric

I keep getting this error and I dont understand why. I checked all CONVERT functions but not sure where is the issue.

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

This is the query and you can see Line 1 does not have any type of conversion.

SELECT 
  'Entity Code|Department Code|Job Code|Pay Code|Pay Period Code|Employee Code|Fiscal Year|Time 
  Class|Unit Type|Amount|Home Entity Code|Home Department|Pay Period End Date' AS PRL_HEADERR
UNION ALL SELECT       
  (ISNULL(RTRIM(LTRIM(RIGHT(REPLICATE(0, 4) + SUB.DIST_COMPANY, 4))), '')    
  + '|' + ISNULL(RTRIM(LTRIM(RIGHT(REPLICATE(0, 4) + SUB.DST_ACCT_UNIT2, 4))), '')   
  + '|' + ISNULL((SUB.JOB_CODE2), '')                      
  + '|' + ISNULL((SUB.PAY_CODE2), '')                      
  + '|' + ISNULL((SUB2.PAYPERIOD), '')                     
  + '|' + ISNULL(
      (CASE WHEN SUBSTRING(RIGHT(REPLICATE(0, 7) + SUB.EMPLOYEE, 7),1,1)='0' 
      THEN SUBSTRING(RIGHT(REPLICATE(0, 7) + SUB.EMPLOYEE, 7),2,LEN(RIGHT(REPLICATE(0, 7) + 
      SUB.EMPLOYEE, 7))) 
      ELSE RIGHT(REPLICATE(0, 7) + SUB.EMPLOYEE, 7) END), '')    
  + '|' +       ISNULL((SUB.FISCAL_YEAR), '')                    
  + '|' +       ISNULL((SUB.TIME_CLASS), '')                     
  + '|' +       ISNULL((SUB.UNIT_TYPE), '')                      
  + '|' +       ISNULL((SUB.UNIT_VALUE), '')                     
  + '|' +       ISNULL(HOME_ENTITY_CODE, '')                     
  + '|' +       ISNULL(HOME_DEPARTMENT, '')                     
  + '|' +     ISNULL(PAY_PER_END_DATE, ''))  AS PRL_HEADER
FROM
 (
  SELECT
     TOP 100 PERCENT DIST_COMPANY,
     LTRIM(RTRIM(DST_ACCT_UNIT)) AS DST_ACCT_UNIT2,
     LTRIM(RTRIM(JOB_CODE)) AS JOB_CODE2,
     LTRIM(RTRIM(PAY_CODE)) AS PAY_CODE2,
     PER_END_DATE,
     EMPLOYEE,
     (CONVERT(VARCHAR(4), GL_DATE, 112)) AS FISCAL_YEAR,
     'A' AS TIME_CLASS,
     'Hours' AS UNIT_TYPE,
     HOURS AS UNIT_VALUE,
     COMPANY AS HOME_ENTITY_CODE,
     LTRIM(RTRIM(HM_ACCT_UNIT)) AS HOME_DEPARTMENT,
     CONVERT(VARCHAR(8), PER_END_DATE,112) AS PAY_PER_END_DATE 
  FROM
     TEST_TABLE
  WHERE
     (
        GL_DATE >= CAST('20200101' AS DATETIME)
        AND GL_DATE <= CAST('20201231' AS DATETIME)
     )
  UNION ALL
  SELECT TOP 1000
     DIST_COMPANY,
     LTRIM(RTRIM(DST_ACCT_UNIT)) AS DST_ACCT_UNIT2,
     LTRIM(RTRIM(JOB_CODE)) AS JOB_CODE2,
     LTRIM(RTRIM(PAY_CODE)) AS PAY_CODE2,
     PER_END_DATE,
     EMPLOYEE,
     (CONVERT(VARCHAR(4), GL_DATE, 112)) AS FISCAL_YEAR,
     'A' AS TIME_CLASS,
     'Amount' AS UNIT_TYPE,
     WAGE_AMOUNT AS UNIT_VALUE,
     COMPANY AS HOME_ENTITY_CODE,
     LTRIM(RTRIM(HM_ACCT_UNIT)) AS HOME_DEPARTMENT,
     CONVERT(VARCHAR(8), PER_END_DATE,112) AS PAY_PER_END_DATE 
  FROM
     TEST_TABLE
  WHERE
     (
        GL_DATE >= CAST('20200101' AS DATETIME)
        AND GL_DATE <= CAST('20201231' AS DATETIME)
     )
  ORDER BY
     EMPLOYEE,
     PER_END_DATE 
 )
 AS SUB 
 INNER JOIN
  (
     SELECT
        ROW_NUMBER() OVER (ORDER BY PP_END_DATE) AS ROWNUM,
        SUB3.PP_END_DATE,
        (
           (CONVERT(VARCHAR(4), SUB3.PP_END_DATE, 112) + '-') + ROWNUM
        )
        AS PAYPERIOD
     FROM
        (
           SELECT DISTINCT
              TOP 100 PERCENT PER_END_DATE AS PP_END_DATE 
           FROM
              TEST_TABLE
           WHERE
              (
                 PER_END_DATE >= CAST('20200101' AS DATETIME)
                 AND PER_END_DATE <= CAST('20201231' AS DATETIME) 
              )
           ORDER BY
              PER_END_DATE 
        )
        AS SUB3 
  )
  AS SUB2 
  ON SUB2.PP_END_DATE = SUB.PER_END_DATE

sql server – Japanese_Unicode_CI_AS japenese value in varchar

I am loading a table with Japanese name frin CSV those are changing into a question mark. Table has varchar column – I know that varchar column is not Unicode that’s the reason it is happening. However, how come existing value written Japanese are store in varchar while ideally is should be in nvarchar

Is there a way to convert nvarchcar to varchar. Database is out of our control and we can change schema.

order by – Sort varchar by its numeric fields

I’m sure this has been asked before, but since the question can be formulated in many different ways it’s difficult to find the proper answer.

I have an orders table with a varchar field for the order number, which is formatted with 4-digits year, a dash (-), and a progressive numeric value. For example it may contain the following values:

SELECT number FROM orders ORDER BY number LIMIT 10;

  number  
----------
 1999-13
 2019-11
 2020-1
 2020-10
 2020-100
 2020-12
 2020-2
 2020-21
 2020-3
 2021-1

I need to sort that field by year and then by the progressive number, with this expected result:

  number  
----------
 1999-13
 2019-11
 2020-1
 2020-2
 2020-3
 2020-10
 2020-12
 2020-21
 2020-100
 2021-1

My questions are:

  1. Which is the simplest way to achieve this with an ORDER subclause?
  2. How to add an efficient index using this custom sort without having to modify the table?

If different best answers are possible for different DBMS/versions, let’s assume PostgreSQL 12.

mysql – How to increase price by 20% when value entered in SQL is VARCHAR

I am trying to increase the price of all items that are monitors by 20% but the current line is VARCHAR so when I try to increase by 20% I get the following error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

This was my data that I entered following steps on another post here

update Product
set ProdPrice = ‘price * 1.2’
where ProdName like ‘%monitor%’

Here is what the full table looks like:
enter image description here

sql server – Is the CONCAT function potentially worse performance wise compared to using the + operator in a varchar predicate?

Since CONCAT() is a function, would using it in a predicate have a higher potential of causing cardinality issues and resultantly poorer performance, as opposed to using the plus operator when concatenating varchars together?

Two logically equivalent SQL examples:

SELECT *
FROM Table1
INNER JOIN Table2
    ON Table1.SomeVarCharField LIKE CONCAT('%', Table2.SomeOtherVarCharField, '%')

vs

SELECT *
FROM Table1
INNER JOIN Table2
    ON Table1.SomeVarCharField LIKE '%' + Table2.SomeOtherVarCharField + '%'

postgresql – Performance of indexing varchar or text as an alternative to UUIDs?

I was reading up on nanoid and was possibly considering using the generated ids as the primary key for a table. The generated IDs are strings such as V1StGXR8_Z5jdHi6B-myT.

In my research I came across the following comment:

One of the benefits at least in postgres is that uuid v4 can be treated like a native type when stored which makes it fast to search.

Is it necessarily true that a primary key based on a UUID column would be more performant than a primary key based on a text or varchar column? If so, is there some other data type I can use to store nanoids which would match the performance of the native UUID type?

SQL server – Store VarChar (Max) [Notes] Column on an identical or separate table

While it's great that you even think about it – I consider it to be micro-optimization. SQL Server has already integrated management optimizations NVARCHAR(MAX) and therefore in general I would say not to guess it until it becomes a problem. However, two main points came to my mind when reading your question.

The first is (as you touched on with your last point) would there really be performance gained by dividing the data into another table by letting SQL Server optimize the pages and the data itself? I don't have any hard evidence with me right now but I don't think so. The reason is that, as you mention, your data regularly exceeds 8,000 bytes and will therefore be moved to the LOB pages of your grade table anyway. You would simply move the perceived problem from one table to another. Add the overhead costs of running a JOIN when you need to recover the data and you will probably find yourself a little worse than you originally had.

Second, since you labeled the question with the design of the database, my actual recommendation is that you to do divide the notes into another table. However, my reasoning is not so much motivated by optimizing performance as by adding more specific flexibility to the application. It is very likely that a shipment may need more than one note attached to it (for example if it is delayed during transit, or if a customer calls, etc.). If you have the note on the shipment itself, it is more difficult to do so without duplicating the data. If you extract it to its own table, you can assign multiple notes to a shipment and track who entered it and when. This makes your application and your database more flexible.

If you want to optimize, I recommend removing the need to query notes, unless someone specifically wants to view them (like having a dedicated form in your app to do so). That way, you only need to join / search when someone explicitly wants to display the data. Even if you have not externalized the notes to another table, not including the column in the query if it is not necessary will remove the need for SQL Server to read the LOB page. If possible, I always optimize queries to return the least amount of information required before optimizing at the micro level.

SQL-SERVER: how to get the maximum length of a variable of VARCHAR help

the query that i've tried to do is

Hello, in this question im trying to get the maximum length of a name but i cnat seem to figure out what im doing wrong, i’ve search the web for answer but find none. i tried to put the MAX function in the select clause but it didnt seem to fix it, what am i doing wrong?? Thank you