for a project I am working with a table of zip-codes that I imported from a *.csv file, exported form Excel. Those zipcodes all have 5 numbers and are formatted as text within my database. Some of them should start with 0, however during the export/import process this 0 got lost, leaving me with zip-codes that only have 4 instead of 5 digits where the leading 0 went missing. Is there a way an SQL command might add this zero to strings only having 4 digits? Thank you

I would like to query the integer part and fractional part of all decimal(8,4) values in a column. By that I mean, I want to know the actual given values, and not the max allowed by the data type, which are integer = 4, fractional = 4. For example, 33.99 would return (2,2) and 1.375 would return (1,3). I have started trying to parse the numbers as strings using char_length() like this: SELECT max(char_length(SUBSTRING_INDEX(col,'.',1))), max(char_length(SUBSTRING_INDEX(col,'.',-1))); A similar, more intuitive, and uglier way in Oracle is SELECT max(length(regexp_substr(col,'^.*[.]',1))), max(length(regexp_substr(col,'[.].*$',1))) But is there a better way that exploits MySQL's knowledge that these are **actually numbers**? This page implies that the true lengths of numbers - e.g. without leading 0s - is known and retrievable, so I was hoping there was a function that would just do this. Does this have something to do with the assertion that "MySQL returns all data as strings and expects you to convert it yourself" or is that specific to that Python module?

I'm trying to use Microsoft Access to start an employee database. However, I want the display of the Employee ID to be the initials and then their number. For example, John Smith with the ID 0002 is JS0002. I've been playing around with format but it doesn't seem I can do this if I put my EmployeeID as an autonumber. Thanks in advance!

How is it possible to convert a float number of seconds to HH:MM:SS in Informix? I have a column that has a run duration of 1449.448520410. I want to convert this to a human-readable format. I have identified that running the below gives close to what I want, but excludes the hours:

select b.run_duration, 
       floor(run_duration / 60) || ':' || lpad(mod(run_duration, 60), 2, '0') as run_duration_time 
from ph_task a, ph_run b 
where a.tk_id = b.run_task_id 
order by run_duration DESC

Output:

-none
24:09

What I would like to see is:

-none
00:24:09

How can I customize my SQL to provide that?

I have a database with table name student. I would like to display the register_number and phone_number of the student. The phone_number should be in the following format: +91-123-456-7890 and if the phone_number is NULL, then it should display N/A. The table looks like: R_NO | STUDENT_NAME | PHONE_NUMBER ------------------------------------------------------- 1 | Rajesh | 9632545123 2 | Sridevi | 9512647359 3 | Shiva | 9632155862 4 | HariHaran | 8426911231 5 | Ravi | 9111558899 6 | Pauline | NULL

I have the following query that I am toying with:

SELECT
    to_char(date_trunc('hour', "TimeStamp"), 'HH12 AM') || ' - ' || to_char(date_trunc('hour', "TimeStamp")  + interval '1 hour', 'HH12 AM') as time,
    COUNT(*) as totals
FROM 
    eventtotals
WHERE "TimeStamp" >= NOW() - Interval '24 HOURS'
GROUP BY date_trunc('hour', "TimeStamp")

It displays the following output:

TIME			    TOTAL
04 PM - 05 PM		300
05 PM - 06 PM		452
06 PM - 07 PM		393
07 PM - 08 PM		356
08 PM - 09 PM		356
09 PM - 10 PM		361
10 PM - 11 PM		359
11 PM - 12 AM		367
12 AM - 01 AM		357
01 AM - 02 AM		360
02 AM - 03 AM		286

I am aware of:

TRIM(LEADING '0'...

However, I would like to remove the leading zeros from the hours, I am just unsure of where to actually put the line for trimming in the above listed query, unless there is another way.

I have *cent* values, retained as integers, that I need to convert to *dollars* and show them as a decimals. **Example** A value is kept as 1925, so I need to be able to return 19.25. This is what I had been doing in MySQL format(price/ 100, 2) as amount and when summing the value, I use cast(SUM(price) / 100 AS DECIMAL(12, 2)) as total_amount These seem to work fine, but when moving over to Redshift, I am not sure if I am using the right function. **Current attemtp** In Redshift, I try with price :: DECIMAL(12, 2) as amount and then SUM(price)/ 100 :: DECIMAL(12, 2) as total_amount In both instances, I get 4 decimal points in my results? Is using wrapping these functions with trunc(,2) a good way to solve this?

I've started a new job and it involves looking at a bunch of big numbers. Is there an easy way to add commas to an int or decimal field to make it readable? For example, SQL Server outputs the column on the left, but for my own sanity, I need it to look like the one on the right: 2036150 -> 2,036,150 ...or would I have to write some heinous left(right(vandalized_data),6),3) + ',' + right(left(vandalized_data),6),3) function? The perfect thing would be commas in the display grid, then plain integers in the output.

I'm trying to wrap my head around how Postgres represents numbers as binary. How would I format a fixed-length number as a string of ones and zeroes? Example:

select fmt((1<<0)::int2); -- something like 0000 0000 0000 0001
select fmt((1<<2)::int2); -- something like 0000 0000 0000 0100

EDIT: I'm working on postgres 14.

SQL Server 2016, SQL Server Management Studio (SSMS). Creating external tables with Transact-SQL and Polybase. How can we read decimals with a decimal mark, comma instead of dot? When using float and decimal, it fails and throws an error. When using money data type it succeeds, but misreads decimals and integers are returned instead. Revised the file format standard clause, no option to configure such a thing. Collation already set for my region. Sample data:

-none
2017;4;2017;601PPP;183,63;0
2017;4;2017;601PPP;183,63;0
2017;4;2017;601PPP;183,63;1.000,55
2017;4;2017;601PPP;183,63;2,5
2017;4;2017;601PPP;183,63;7,5
2017;4;2017;601PPP;405,28;17,5

Example of a create table that would fail to read the sample data: CREATE EXTERNAL TABLE [dbo].[STG_Table] ( field1 smallint NULL ,field2 tinyint NULL ,field3 smallint NULL ,fieldn_2 varchar(10) NULL ,fieldn_1 money NULL ,fieldn float NULL ) WITH (LOCATION='/STG_Table/', DATA_SOURCE = AzureDataSource, FILE_FORMAT = FileFormat ); Query used to read data: SET @dynamicSQL = N'SELECT * INTO ##' + @someTable + ' FROM STG_' + @someTable EXEC sp_executesql @dynamicSQL Specifying fields implies more cases to be added (working inside a procedure) and it can be avoided by configuring such a thing. I found more simple configuring this, than adding more code. No configuration found in database configuration.

I came across some weird behavior: While passing a float value into a varchar column, the values are getting converted from integers into scientific notation, and it's that scientific notation that gets stored as a string. if OBJECT_Id('tempdb..#whydis') is not null begin drop table #whydis end if OBJECT_Id('tempdb..#ImSeriously') is not null begin drop table #ImSeriously end create table #whydis (bigID float) create table #ImSeriously (bigID varchar(255)) insert into #whydis(BigID) values(1495591), (1495289), (1495610), (1495611), (1495609), (1495592), (1495686) INSERT INTO #ImSeriously (bigID) SELECT BigID from #whydis select * from #ImSeriously results look like this: 1.49559e+006 Scientific notation stored as a string. It's easy enough to work around by casting as int: INSERT INTO #ImSeriously (bigID) SELECT cast(BigID as int) from #whydis But the whole thing has me scratching my head. **Question:** What is it about floats that stores them this way?

For example, I want to convert the number. select convert(1354256,money) salMoney result: 1354256 convert to 1,354,256

What datatype does this error message refer to? > ERROR: numeric field overflow > DETAIL: A field with precision 1, scale 0 must round to an absolute value less than 10^1. I'm getting the error listed above, it's coming from an insert trigger. Since none of my datatype are explicitly defined as NUMERIC(1,0) and since the maximum value is 10 to the power of 1 (i.e. 10) I'm guessing that this error is actually referring to a BOOLEAN field. However, I can't find confirmation of this. UPDATE Solved. My trigger is attempting to parse some strings into numbers. The error listed is returned when to_number() doesn't get a string in the expected format. This returns 1.4 (correct) - select to_number('1.4','9D9'); This returns the aforementioned error select to_number('1d4','9D9'); Still not quite sure why it's complaining about NUMERIC(1,0) but at least I know *why* there's an error.

A common and useful paradigm of programming is to separate content and presentation. One system deals with building the result, another system deals with outputting it. You see this with HTML vs CSS. HTML should ideally be clean and without any internal styling (color, position, etc), which should be handled by CSS. I'm now wondering how this applies to SQL results. I often find myself formatting results in a query: ~~~SQL SELECT CONCAT(first_name, ' ', last_name) AS name, DATE_FORMAT(last_edit, '%d/%m/%Y') AS last_edit, CONCAT( record_type, IF (private = 1, ' (Private)', ' (Public)') ) AS record_type FROM people ORDER BY updated DESC, LastEdit DESC ~~~ Three different scenarios are covered here: 1. The first column is a simple concatenation. 2. The second transforms the output into a different format. 3. The third actually adds content to the result. I understand that there are many useful functions for transforming data in a query. But from a team development point of view, should I be formatting these results in the presentation layer rather than the logic layer (i.e., in my PHP code instead of the SQL query)? If so, then how far should I take it? Are columns 1 and 2 acceptable but 3 isn't? Is there a widely-accepted best practice regarding this?

Hi there was wondering if someone could help me out, and can give me a little code example. i have a table named "projecten", and columns named "pstatus and pafgifte" these columns have numbers only. I would like to count up all "pafgifte" with "pstatus = 2" only, and echo result. help would be appreciated.

I need to store the exact value of a decimal: For example 0.00500 and 0.0050. If I use decimal (10, 5), I receive 0.00500 every time. If I enter 0.0050, I still get 0.00500

I have a column of type BIGINT in my MonetDB database and for readability I'd like to print its contents as hexadecimal number when executing a SELECTstatement. Is there any way to achieve this? I read through the MonetDB documentation, looking for a function such as HEX(), CONV() etc. but could not find anything like that (or close). I even read through the defined functions (SELECT name FROM sys.functions;) with no luck. P.S. I'm not allowed to create the tag *monetdb* here due to lack of reputation. Could someone please add this tag for me? (This is quite annoying since I am forced to select a tag on none of the existing seems appropriate to me here…)

I have some data stored as an unsigned tinyint. When displaying it I want it to be zero padded only if it is 1 digit in length. I have issues with LPAD(CAST(NUMBER AS CHAR CHARSET UTF8),2,'0') when I have three digit long numbers. How do I get what I'm looking for? Example of data in table: NUMBER 1 2 3 ... 10 11 ... 101 102 What I currently get when running with LPAD LPAD_NUMBER 01 02 03 ... 10 11 ... 10 10 What I want UNKNOWN 01 02 03 ... 10 11 ... 101 102 **SOLUTION I USED** ============ select case when number between 0 and 9 then LPAD(CAST(number AS CHAR CHARSET UTF8), 2, '0') else CAST(number AS CHAR CHARSET UTF8) end as newNum; This gets me what I'm looking for and is based off of [the accepted answer](https://dba.stackexchange.com/a/219692/118925) .

According to the Oracle documentation, NLS_NUMERIC_CHARACTERS stores the decimal marker used by the current session. To get the current NLS_NUMERIC_CHARACTERS, I use this request: select value from nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS'; And I get this result: VALUE , So my decimal marker is 'comma'. But when I make a SELECT request to get a decimal value, the decimal marker return by Oracle is different. If I run this dummy request: select 3/2 decimal_value from dual; I get this result: DECIMAL_VALUE 1.5 So, my questions are: why is it not 1,5 as expected? What need I change to get the decimal marker as defined is NLS_NUMERIC_CHARACTERS? **Important note:** I can't change my SELECT request. ---------- After reading this article (suggested by @EdStevens in the comments), here are some precisions. According to the article, the decimal marker is defined by : 1. The NLS values defined in the database initialization parameters 2. The NLS settings of the OS of the client 3. The NLS settings of the Oracle client 4. The parameters of the TO_CHAR function if it is used in the SELECT statement In our case, because we don't use TO_CHAR function in the SELECT statement, the decimal marker should be defined by the NLS settings of the Oracle client (NLS_NUMERIC_CHARACTERS) but we saw above that is not the case. Interresting fact, if I use TO_CHAR function in my SELECT statement: select TO_CHAR(3/2) decimal_value from dual; I get this result: DECIMAL_VALUE 1,5 If nlsparam is omitted, the documentation says: > If you omit nlsparam or any one of the parameters, then this > function uses the default parameter values for your session. So, when we use TO_CHAR function, Toad use the NLS value of the session but not in the others cases. Is it possible that Toad performs an implicit conversion (NUMBER -> CHAR) before printing the value in the data grid without using NLS parameters? If it is the case, why this implicit conversion don't use NLS_NUMERIC_CHARACTERS? What can I do to get my number in the correct format? ---------- Here is a little recap of the informations present in the comments: - The problem I expose above is made with Toad for Oracle (version 9.1.0.62) but I also reproduce this issue on the application I develop who uses OCI drivers. - The session value of NLS_TERRITORY is France, NLS_LANGUAGE is French and NLS_LANG is FRENCH_FRANCE.WE8MSWIN1252. Those values are defined in the Windows registry. - If I use SQL*Plus, I get a correct result. Indeed the decimal marker corresponds to the one defined in NLS_NUMERIC_CHARACTERS and if I change this value, it directly changed the decimal marker I get (that is not the case with Toad).

I'd like to quickly dump some context into an error message including some boolean flags using the handy-dandy substitution parameters of RAISERROR such as CREATE PROCEDURE MyProc ( @param1 INT = 1, @param2 VARCHAR(255) = 'hello world' ) AS BEGIN ... RAISERROR('Inconsistent state occurred processing @param1=%d @param2=%s', 16, 1, @param1, @param2) ... END According to the documentation there is no type specification for bit type (boolean), but notes that > These type specifications are based on the ones originally defined for the printf function in the C standard library. And as this post notes, > ... since any integral type shorter than int is promoted to int when passed down to printf()s variadic arguments, you can use %d But it does not work in SQL, as I get the following error (2748): > Cannot specify bit data type (parameter ###) as a substitution parameter. Am I forced to use a helper variable? Thanks. P.S. Using compatibility mode for 2008R2.