1

How can I combine functions with strings to end up with a whole in one sentence?

I mean something like this (expected result):

Today is the 3rd day of the year, the 2nd week of the year and the 1st day of the week.

Below is my code, but it separates the functions into separate fields and I don't really know how to put the strings into it yet:

dayofyear(current_date()), weekofyear(current_date()), dayofweek(current_date()); 

3 Answers 3

1

This is translation and display logic. What you're doing is marking up a number for display in one language: English. Though this will get the job done, it is a bad practice. What you probably want is a framework that supports either

You can see what that would like here with ICU MessageFormat using the selectordinal in the React docs (it's actually an example). This is also supported by Angular. If you use a framework that supports this you can support multiple languages with less work. Adopting one though will require the number be passed to the framework. This also gets these lengthy English (not-string) operations out of SQL entirely.

0

I would solve This with a function, else you have to check every number with the case when clause, like seen in the last query

CREATE FUNCTION ending( _num INT )
RETURNS VARCHAR(2)
DETERMINISTIC
BEGIN
DECLARE _ending VARCHAR(2);
SET _ending := 


  CASE RIGHT(_num,1)
      WHEN '1' THEN  IF(LENGTH(_num)  = 1,'st',IF(MID(_num,-2, 1) = 1,'th','st'))
      WHEN '2' THEN  IF(LENGTH(_num)  = 1,'nd',IF(MID(_num,-2, 1) = 1,'th','nd'))
      WHEN '3' THEN  IF(LENGTH(_num)  = 1,'rd',IF(MID(_num,-2, 1) = 1,'th','rd'))
      ELSE  'th'
  END;
RETURN(_ending);
END
SELECT CONCAT('Today is the ',dayofyear(current_date()),ending(dayofyear(current_date()))
,' day of the year, the ', weekofyear(current_date()),ending(weekofyear(current_date()))
,' week of the year and the ',dayofweek(current_date()),ending(dayofweek(current_date())),' day of the week.') As mytext; 
| mytext                                                                                     |
| :----------------------------------------------------------------------------------------- |
| Today is the 151st day of the year, the 22nd week of the year and the 2nd day of the week. |
SELECT 
    CONCAT('Today is the ',
            DAYOFYEAR(CURRENT_DATE()),
            CASE RIGHT(DAYOFYEAR(CURRENT_DATE()), 1)
      WHEN '1' THEN  IF(LENGTH(CURRENT_DATE())  = 1,'st',IF(MID(CURRENT_DATE(),-2, 1) = 1,'th','st'))
      WHEN '2' THEN  IF(LENGTH(CURRENT_DATE())  = 1,'nd',IF(MID(CURRENT_DATE(),-2, 1) = 1,'th','nd'))
      WHEN '3' THEN  IF(LENGTH(CURRENT_DATE())  = 1,'rd',IF(MID(CURRENT_DATE(),-2, 1) = 1,'th','rd'))
                ELSE 'th'
            END,
            ' day of the year, the ',
            WEEKOFYEAR(CURRENT_DATE()),
            CASE RIGHT(WEEKOFYEAR(CURRENT_DATE()), 1)
      WHEN '1' THEN  IF(LENGTH(CURRENT_DATE())  = 1,'st',IF(MID(CURRENT_DATE(),-2, 1) = 1,'th','st'))
      WHEN '2' THEN  IF(LENGTH(CURRENT_DATE())  = 1,'nd',IF(MID(CURRENT_DATE(),-2, 1) = 1,'th','nd'))
      WHEN '3' THEN  IF(LENGTH(CURRENT_DATE())  = 1,'rd',IF(MID(CURRENT_DATE(),-2, 1) = 1,'th','rd'))
                ELSE 'th'
            END,
            ' week of the year and the ',
            DAYOFWEEK(CURRENT_DATE()),
            CASE RIGHT(DAYOFWEEK(CURRENT_DATE()), 1)
      WHEN '1' THEN  IF(LENGTH(DAYOFWEEK(CURRENT_DATE()))  = 1,'st',IF(MID(DAYOFWEEK(CURRENT_DATE()),-2, 1) = 1,'th','st'))
      WHEN '2' THEN  IF(LENGTH(DAYOFWEEK(CURRENT_DATE()))  = 1,'nd',IF(MID(DAYOFWEEK(CURRENT_DATE()),-2, 1) = 1,'th','nd'))
      WHEN '3' THEN  IF(LENGTH(DAYOFWEEK(CURRENT_DATE()))  = 1,'rd',IF(MID(DAYOFWEEK(CURRENT_DATE()),-2, 1) = 1,'th','rd'))
                ELSE 'th'
            END,
            ' day of the week.') AS mytext; 
| mytext                                                                                     |
| :----------------------------------------------------------------------------------------- |
| Today is the 151st day of the year, the 22nd week of the year and the 2nd day of the week. |

db<>fiddle here

2
  • 11 and 12 are still broken.
    – Rick James
    May 31, 2021 at 15:43
  • 2
    it is urgent to me to reform all spoken and dead languages
    – nbk
    May 31, 2021 at 16:19
0

SQL was not designed to handle strings and indeed, doesn't do it very well, compared to Python or Perl say - it has being making strides in recent years with REGULAR EXPRESSIONs and the like, but as you'll see below, it ain't no stroll in the park! MySQL is one of the worst offenders in terms of non-standard behaviour in this area.

First, let's take the relatively simple (all the code below is available on the fiddle here):

SELECT DAYOFYEAR(CURRENT_DATE()) AS "Day in year";

Result:

Day in year
150

"Great", you say, "That's exactly what I want!" (at time of writing it is 2021-05-31). So, it's day 150 of the year.

However, your problems start when you begin to try and concatenate strings using MySQL, especially those with numbers in them.

So, we try:

SELECT 
   DAYOFYEAR(CURRENT_DATE()) +  
  WEEKOFYEAR(CURRENT_DATE()) + 
   DAYOFWEEK(CURRENT_DATE()) AS "Today";

Result:

Today
172

What MySQL has done (WTF!) is add your 150 to 21 (it being the 21st week of the year) and then added a further 1, it being Sunday which is first (in this part of the world - but this will be locale dependent. I can even add 10 (the number, not the string) to it and obtain 182 (see the fiddle...). What's happening there is that there's an implicit conversion going on... it's a complete breach of the Principle of Least Astonishment...

So, we say that we'll try the SQL standard string concatenation operator (||) - double-pipe.

SELECT 
   DAYOFYEAR(CURRENT_DATE()) ||  
  WEEKOFYEAR(CURRENT_DATE()) || 
   DAYOFWEEK(CURRENT_DATE()) AS "Today";

Result:

Today
    1

1?? Again, WTF? Quite what's going on there is anyone's guess - did I mention that MySQL has a non-standard way of doing things sometimes (or maybe, sometimes it does things in a standard way?).

Anyway, there is the relatively widespread CONCAT() which isn't too bad:

SELECT 
  CONCAT
  (
    dayofyear(current_date()),
    weekofyear(current_date()),
    dayofweek(current_date())
  ) AS "Today";

Result:

Today
150211

So, we have 150 for the day, 21 for the week and 1 for the day, exactly as we want.

So, now we convert suitable numbers to suitable ordinals (there is also a function in the same thread) and, voila!

SELECT
  CONCAT
  (
    "The ",
    CONCAT
    (
      DAYOFYEAR(CURRENT_DATE()), 
      CASE
        WHEN DAYOFYEAR(CURRENT_DATE()) % 100 BETWEEN 11 AND 13 THEN "th"
        WHEN DAYOFYEAR(CURRENT_DATE()) %  10 = 1 THEN "st"
        WHEN DAYOFYEAR(CURRENT_DATE()) %  10 = 2 THEN "nd"
        WHEN DAYOFYEAR(CURRENT_DATE()) %  10 = 3 THEN "rd"
        ELSE "th"
      END
    ),
    " Day, the ",
    CONCAT
    (
      WEEKOFYEAR(CURRENT_DATE()), 
      CASE
        WHEN WEEKOFYEAR(CURRENT_DATE()) % 100 BETWEEN 11 AND 13 THEN "th"
        WHEN WEEKOFYEAR(CURRENT_DATE()) %  10 = 1 THEN "st"
        WHEN WEEKOFYEAR(CURRENT_DATE()) %  10 = 2 THEN "nd"
        WHEN WEEKOFYEAR(CURRENT_DATE()) %  10 = 3 THEN "rd"
        ELSE "th"
      END
    ),
    " Week, on the ",
    CONCAT
    (
      DAYOFWEEK(CURRENT_DATE()), 
      CASE
        WHEN DAYOFWEEK(CURRENT_DATE()) % 100 BETWEEN 11 AND 13 THEN "th"
        WHEN DAYOFWEEK(CURRENT_DATE()) %  10 = 1 THEN "st"
        WHEN DAYOFWEEK(CURRENT_DATE()) %  10 = 2 THEN "nd"
        WHEN DAYOFWEEK(CURRENT_DATE()) %  10 = 3 THEN "rd"
        ELSE "th"
      END
    ),
" Day of the Week, in this,", '\n',  "the Year of Our Lord, ",
YEAR(CURRENT_DATE()),
", you are hereby sentenced to die!"
) AS "Death Sentence!";

Result:

Death Sentence!
The 150th Day, the 22nd Week, on the 1st Day of the Week, in this,
the Year of Our Lord, 2021, you are hereby sentenced to die!

The '\n' in the SQL is a carriage return so that the Death Sentence appears over two lines and is more legible.

Be very careful with your brackets and indenting!

2
  • a function is better for that i think
    – nbk
    May 30, 2021 at 18:20
  • @nbk - agreed in the long run. However, the OP was asking about concatenating strings in general and the fact that they were having difficulty even combining 3 different fields into the one would imply to me that functions are a bit down the line yet... :-)
    – Vérace
    May 30, 2021 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.