New answers tagged

10

I would suggest storing the number as a BIGINT, and simply displaying the number the way you want it. DECLARE @Num BIGINT; SET @Num = 2421402015; SELECT CONVERT(VARCHAR(11), REPLICATE('0', 11 - LEN(@Num))) + CONVERT(VARCHAR(11), @Num); As pointed out by Aaron Bertrand in his comment, it is better performance-wise to not calculate the length of the ...


5

To demonstrate that what you're saying in your question is not true: DECLARE @x TABLE(y VARCHAR(11)); INSERT @x(y) VALUES('02421402015'); SELECT y FROM @x; Results: y ----------- 02421402015 You need to tell us more details about what is going on so we can understand your problem better. But most importantly, you need to consider separating storage ...


3

The problem is in the NLS_NCHAR_CHARACTERSET = UTF8. I simulated the same table in a different database with NLS_NCHAR_CHARACTERSET = AL16UTF16. The describe statement shows the correct length as defined while creating table. UTF8 can be 1, 2, 3 or 6 bytes / character, any AL32UTF8 character that is 4 bytes will be stored as 2 time 3 bytes in UTF8. If you ...


2

I'm quite sure that this is a problem of SQL Developer... Retry to desc table after issuing: alter session set NLS_LENGTH_SEMANTICS='CHAR'; Is correct now? If not try to desc the table using sqlplus on the database server bypassing the Oracle Listener: >export NLS_LANG=AMERICAN_SWITZERLAND.AL32UTF8 >export ORACLE_SID=<YOURINSTANCESID> ...


4

Up until 9.4 You can go and ask the system catalogs (pg_type, to be precise): SELECT rolname FROM pg_type t JOIN pg_authid r ON typowner = r.oid WHERE typname = 'bla'; usename ───────── dezso From PostgreSQL 9.5 on From this version on, there is a new object identifier type called regrole, and it makes the query a bit simpler: SELECT ...


2

You're mixing column types. Your CASE expression wants to produce something with a particular type, and in your case is trying to convert everything to bigint. I think your best option is to seperate each column out, by inserting lots of END, CASE lines. Like this: CASE WHEN @SortAttribute = N'State' THEN [State] END, CASE WHEN @SortAttribute = ...



Top 50 recent answers are included