Newbe Dev Stack

Answer : You can chain REPLACE functions: select replace(replace('hello world','world','earth'),'hello','hi') This will print hi earth . You can even use subqueries to replace multiple strings! select replace(london_english,'hello','hi') as warwickshire_english from ( select replace('hello world','world','earth') as london_english ) sub Or use a JOIN to replace them: select group_concat(newword separator ' ') from ( select 'hello' as oldword union all select 'world' ) orig inner join ( select 'hello' as oldword, 'hi' as newword union all select 'world', 'earth' ) trans on orig.oldword = trans.oldword I'll leave translation using common table expressions as an exercise for the reader ;) Cascading is the only simple and straight-forward solution to mysql for multiple character replacement. UPDATE table1...

Answer : Something like Decimal(19,4) usually works pretty well in most cases. You can adjust the scale and precision to fit the needs of the numbers you need to store. Even in SQL Server, I tend not to use " money " as it's non-standard. The only thing you have to watch out for is if you migrate from one database to another you may find that DECIMAL(19,4) and DECIMAL(19,4) mean different things ( http://dev.mysql.com/doc/refman/5.1/en/precision-math-decimal-changes.html ) DBASE: 10,5 (10 integer, 5 decimal) MYSQL: 15,5 (15 digits, 10 integer (15-5), 5 decimal) Assaf's response of Depends on how much money you got... sounds flippant, but actually it's pertinant. Only today we had an issue where a record failed to be inserted into our Rate table, because one of the columns (GrossRate) is set to Decimal (11,4), and our Product department just got a contract for rooms in some amazing resort in Bora Bora, that sell for several million Pacif...

Answer : Leaving SQL aside for a moment... A relational operator takes one or more relations as parameters and results in a relation. Because a relation has no attributes with duplicate names by definition, relational operations theta join and natural join will both "remove the duplicate attributes." [A big problem with posting examples in SQL to explain relation operations, as you requested, is that the result of a SQL query is not a relation because, among other sins, it can have duplicate rows and/or columns.] The relational Cartesian product operation (results in a relation) differs from set Cartesian product (results in a set of pairs). The word 'Cartesian' isn't particularly helpful here. In fact, Codd called his primitive operator 'product'. The truly relational language Tutorial D lacks a product operator and product is not a primitive operator in the relational algebra proposed by co-author of Tutorial D, Hugh Darwen**. This is because th...

Answer : Problem was apparently not the SQL server, but the NAV system that updates the field. There is a compression property that can be used on BLOB fields in NAV, that is not a part of SQL Server. So the custom compression made the data unreadable, though the conversion worked. The solution was to turn off compression through the Object Designer, Table Designer, Properties for the field (Shift+F4 on the field row). After that the extraction of data can be made with e.g.: select convert(varchar(max), cast(BLOBFIELD as binary)) from Table Thanks for all answers that were correct in many ways! The accepted answer works for me only for the first 30 characters. This works for me: select convert(varchar(max), convert(varbinary(max),myBlobColumn)) FROM table_name It depends on how the data was initially put into the column. Try either of these as one should work: SELECT CONVERT(NVarChar(40), BLOBTextToExtract) FROM [NavisionSQL$Customer]; Or if it was just varchar ... ...

Answer : InfoPlus21 is process historian containing list of templates of different tag structure e.g. IP_AnalogDef, IP_DescreteDef, IP_TextDef etc. Based on process tags from DCS/OPC/Any other historian, the IP21 records are created and each record acts as a table in historian. ANS1: Aspentech software is only windows based compatibility however IP21 aspenONE Process Explorer is web based and therefore you can access it over any operating system using host url. ANS2: you can try SELECT statement to get data from IP21 Historian using it's end-user component SQLPlus or on excel add-ins. e.g. SELECT NAME, IP_DESCRIPTION, IP_PLANT_AREA, IP_ENG_UNITS FROM IP_ANALOGDEF RESULTS: I hope this help you understand better. Otherwise you need to first learn the structure of your IP21 historian tags to build the query e.g. If it has customized structure, then you have to build your own. Welcome in industrial-IT ! For these technology, the best option is the 'AspenTech...

Answer : From my experience -- Sure, you can use MySQL Workbench with MariaDB. However, I have tried basic functionalities only, like queries, schema design etc. Not sure about compatibility of advanced features. Just to list a few other options: MySQL Workbench Heidi Sql SQLyog So my experiences are, yes you can use MySQL Workbench for MariaDB database designs. However I needed to change the "Default Target MySQL Version" to 5.7 . This can be done by going to: Edit->Preferences in the menu. And finally to Modeling->MySQL. Since the latest MySQL version, v8.x, the SQL statements are not compatible with MariaDB statements (like creating an index). MariabDB creating an index on a table: INDEX `fk_rsg_sub_level_rsg_top_level1_idx` (`rgs_top_level_id` ASC) vs MySQL: INDEX `fk_rsg_sub_level_rsg_top_level1_idx` (`rgs_top_level_id` ASC) VISIBLE MariaDB can't handle this VISIBLE keyword in this example. Using an old MySQL Version, MySQL Workbench...

Answer : Add an identity to your table variable, and do an easy loop from 1 to the @@ROWCOUNT of the INSERT-SELECT. Try this: DECLARE @RowsToProcess int DECLARE @CurrentRow int DECLARE @SelectCol1 int DECLARE @table1 TABLE (RowID int not null primary key identity(1,1), col1 int ) INSERT into @table1 (col1) SELECT col1 FROM table2 SET @RowsToProcess=@@ROWCOUNT SET @CurrentRow=0 WHILE @CurrentRow<@RowsToProcess BEGIN SET @CurrentRow=@CurrentRow+1 SELECT @SelectCol1=col1 FROM @table1 WHERE RowID=@CurrentRow --do your thing here-- END DECLARE @table1 TABLE ( idx int identity(1,1), col1 int ) DECLARE @counter int SET @counter = 1 WHILE(@counter < SELECT MAX(idx) FROM @table1) BEGIN DECLARE @colVar INT SELECT @colVar = col1 FROM @table1 WHERE idx = @counter -- Do your work here SET @counter = @counter + 1 END Believe it or not, this is actually more efficient and performant than using a cursor. ...

Answer : Strictly, method a is the least resource intensive: a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) Proven less CPU intensive for the same total duration a million rows by someone with way too much time on their hands: Most efficient way in SQL Server to get a date from date+time? I saw a similar test elsewhere with similar results too. I prefer the DATEADD/DATEDIFF because: varchar is subject to language/dateformat issues Example: Why is my CASE expression non-deterministic? float relies on internal storage it extends to work out first day of month, tomorrow, etc by changing "0" base Edit, Oct 2011 For SQL Server 2008+, you can CAST to date i.e. CAST(getdate() AS date) . Or just use date datatype so no time to remove. Edit, Jan 2012 A worked example of how flexible this is: Need to calculate by rounded time or date figure in sql server Edit, May 2012 Do not use this in WHERE clauses and the like without thinking: adding a function or CAST...

Answer : Given your specifications (plus additional info in the comments), You have a numeric ID column (integer numbers) with only few (or moderately few) gaps. Obviously no or few write operations. Your ID column has to be indexed! A primary key serves nicely. The query below does not need a sequential scan of the big table, only an index scan. First, get estimates for the main query: SELECT count(*) AS ct -- optional , min(id) AS min_id , max(id) AS max_id , max(id) - min(id) AS id_span FROM big; The only possibly expensive part is the count(*) (for huge tables). Given above specifications, you don't need it. An estimate will do just fine, available at almost no cost (detailed explanation here): SELECT reltuples AS ct FROM pg_class WHERE oid = 'schema_name.big'::regclass; As long as ct isn't much smaller than id_span , the query will outperform other approaches. WITH params AS ( SELECT 1 AS min_id...

Answer : if var is NULL then var :=5; end if; Use: IF Var IS NULL THEN var := 5; END IF; Oracle 9i+: var = COALESCE(Var, 5) Other alternatives: var = NVL(var, 5) Reference: COALESCE NVL NVL2 In PL/SQL you can't use operators such as '=' or '<>' to test for NULL because all comparisons to NULL return NULL . To compare something against NULL you need to use the special operators IS NULL or IS NOT NULL which are there for precisely this purpose. Thus, instead of writing IF var = NULL THEN... you should write IF VAR IS NULL THEN... In the case you've given you also have the option of using the NVL built-in function. NVL takes two arguments, the first being a variable and the second being a value (constant or computed). NVL looks at its first argument and, if it finds that the first argument is NULL , returns the second argument. If the first argument to NVL is not NULL , the first argument is returned. So you c...

Answer : Update: Since Django 2.1, everything works out of the box. No workarounds needed and the produced query is correct. This is maybe a bit too late, but I have found the solution (tested with Django 1.11.1). The problem is, call to .values('publisher') , which is required to provide grouping, removes all annotations, that are not included in .values() fields param. And we can't include dbl_price to fields param, because it will add another GROUP BY statement. The solution in to make all aggregation, which requires annotated fields firstly, then call .values() and include that aggregations to fields param(this won't add GROUP BY , because they are aggregations). Then we should call .annotate() with ANY expression - this will make django add GROUP BY statement to SQL query using the only non-aggregation field in query - publisher . Title.objects .annotate(dbl_price=2*F('price')) .annotate(sum_of_prices=Sum('dbl_price')) ....

Answer : Don't use convert - that involves strings for no reason. A trick is that a datetime is actually a numeric, and the days is the integer part (time is the decimal fraction); hence the day is the FLOOR of the value: this is then just math, not strings - much faster declare @when datetime = GETUTCDATE() select @when -- date + time declare @day datetime = CAST(FLOOR(CAST(@when as float)) as datetime) select @day -- date only In your case, no need to convert back to datetime; and using a range allows the most efficent comparisons (especially if indexed): declare @when datetime = 'Feb 15 2012 7:00:00:000PM' declare @min datetime = FLOOR(CAST(@when as float)) declare @max datetime = DATEADD(day, 1, @min) select * from sampleTable where DateCreated >= @min and DateCreated < @max Simple Cast to Date will resolve the problem. DECLARE @Date datetime = '04/01/2016 12:01:31' DECLARE @Date2 datetime = '04/01/2016' SELECT CAST(@Date as date)...

Answer : When you subtract two variables of type TIMESTAMP , you get an INTERVAL DAY TO SECOND which includes a number of milliseconds and/or microseconds depending on the platform. If the database is running on Windows, systimestamp will generally have milliseconds. If the database is running on Unix, systimestamp will generally have microseconds. 1 select systimestamp - to_timestamp( '2012-07-23', 'yyyy-mm-dd' ) 2* from dual SQL> / SYSTIMESTAMP-TO_TIMESTAMP('2012-07-23','YYYY-MM-DD') --------------------------------------------------------------------------- +000000000 14:51:04.339000000 You can use the EXTRACT function to extract the individual elements of an INTERVAL DAY TO SECOND SQL> ed Wrote file afiedt.buf 1 select extract( day from diff ) days, 2 extract( hour from diff ) hours, 3 extract( minute from diff ) minutes, 4 extract( second from diff ) seconds 5 from (select systimes...

Answer : Use the "IsDbNull" method on the data reader... for example: bool? result = dataReader.IsDbNull(dataReader["Bool_Flag"]) ? null : (bool)dataReader["Bool_Flag"] Edit You'd need to do something akin to: bool? nullBoolean = null; you'd have bool? result = dataReader.IsDbNull(dataReader["Bool_Flag"]) ? nullBoolean : (bool)dataReader["Bool_Flag"] Consider doing it in a function. Here's something I used in the past (you can make this an extension method in .net 4): public static T GetValueOrDefault<T>(SqlDataReader dataReader, System.Enum columnIndex) { int index = Convert.ToInt32(columnIndex); return !dataReader.IsDBNull(index) ? (T)dataReader.GetValue(index) : default(T); } Edit As an extension (not tested, but you get the idea), and using column names instead of index: public static T GetValueOrDefault<T>(this SqlDataReader dataReader, string columnName) { return !dataR...

Answer : As described in Cast Functions and Operators: The type for the result can be one of the following values: BINARY[(N)] CHAR[(N)] DATE DATETIME DECIMAL[(M[,D])] SIGNED [INTEGER] TIME UNSIGNED [INTEGER] Therefore, you should use: SELECT CAST(PROD_CODE AS UNSIGNED) FROM PRODUCT For casting varchar fields/values to number format can be little hack used: SELECT (`PROD_CODE` * 1) AS `PROD_CODE` FROM PRODUCT`

Answer : The error Error on rename of ... errno: 150 - Foreign key constraint is incorrectly formed) happens because you are trying to drop a referenced primary key, even though you are disabling foreign key constraint checking with SET FOREIGN_KEY_CHECKS=0; Disabling foreign key checks would allow you to temporarily delete a row in the currency table or add an invalid currencyId in the foreign key tables, but not to drop the primary key. Changing a PRIMARY KEY which is already referenced by other tables isn't going to be simple, since you risk losing referential integrity between the tables and losing the relationship between data. In order to preserve the data, you'll need a process such as: Add a new Foreign key column ( code ) to each FK table Map the code foreign key from the previous currencyId via an update Drop the existing foreign key Drop the old currencyId foreign key column Once all FK's have been dropped, change the primary key on the...

Answer : SQL Server does not currently support subqueries for CHECK CONSTRAINTs. As you have discovered, there can be trouble with CHECK constraints involving UDFs when attempting to circumvent the subquery limitation. The alternative constraint implementation strategies are triggered procedural and embedded procedural . The former is preferred because, in common with declarative constraints, they cannot be circumvented. Implementing a triggered procedural strategy that is well optimized and handles concurrency issues is non-trivial but still doable. I highly recommend the book Applied Mathematics for Database Professionals By Lex de Haan, Toon Koppelaars, chapter 11 (the code examples are Oracle but can be easily ported to SQL Server). As others have mentioned already, this type of Check constraints is not yet implemented in SQL-Server. Besides triggers, you could also examine the possibility of changing the table's design. A possible alternative includes storing the...

Answer : You can use NULLIF function e.g. something/NULLIF(column_name,0) If the value of column_name is 0 - result of entire expression will be NULL Since count() never returns NULL (unlike other aggregate functions), you only have to catch the 0 case (which is the only problematic case anyway): CASE count(column_name) WHEN 0 THEN 1 ELSE count(column_name) END Quoting the manual about aggregate functions: It should be noted that except for count , these functions return a null value when no rows are selected. I realize this is an old question, but another solution would be to make use of the greatest function: greatest( count(column_name), 1 ) -- NULL and 0 are valid argument values Note: My preference would be to either return a NULL, as in Erwin and Yuriy's answer, or to solve this logically by detecting the value is 0 before the division operation, and returning 0 . Otherwise, the data may be misrepresented by using 1 .

Answer : Solution 1: First of all, don't just take my word for it! Test my suggestion out with this: select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' charset=utf8;') from information_schema.TABLES WHERE TABLE_SCHEMA != 'information_schema' limit 10; select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' alter column ',COLUMN_NAME,' charset=utf8;') from information_schema.COLUMNS WHERE TABLE_SCHEMA != 'information_schema' limit 10; If you feel good with the outcome of that, remove the limit clauses and save the output to an SQL script or, get fancy and pipe the output directly to mysql similar to what I demonstrate here. That would look like this: mysql -B -N --host=prod-db1 --user=admin --password=secret -e "select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' charset=utf8;') from information_schema.TABLES WHERE TABLE_SCHEMA != 'information_schema...

Answer : Try this: SELECT availablePlaces, capacity, ROUND(availablePlaces * 100.0 / capacity, 1) AS Percent FROM mytable You have to multiply by 100.0 instead of 100, so as to avoid integer division. Also, you have to use ROUND to round to the first decimal digit. Demo here