How to print VARCHAR(MAX) using Print Statement?
I know it’s an old question, but what I did is not mentioned here. For me the following worked. DECLARE @info NVARCHAR(MAX) –SET @info to something big PRINT CAST(@info AS NTEXT)
sql
Deleting duplicate rows from sqlite database
You need a way to distinguish the rows. Based on your comment, you could use the special rowid column for that. To delete duplicates by keeping the lowest rowid per (hash,d): delete from YourTable where rowid not in ( select min(rowid) from YourTable group by hash , d )
SQL Server: Make all UPPER case to Proper Case/Title Case
This function: “Proper Cases” all “UPPER CASE” words that are delimited by white space leaves “lower case words” alone works properly even for non-English alphabets is portable in that it does not use fancy features of recent SQL server versions can be easily changed to use NCHAR and NVARCHAR for unicode support,as well as any … Read more
sql ORDER BY multiple values in specific order?
… WHERE x_field IN (‘f’, ‘p’, ‘i’, ‘a’) … ORDER BY CASE x_field WHEN ‘f’ THEN 1 WHEN ‘p’ THEN 2 WHEN ‘i’ THEN 3 WHEN ‘a’ THEN 4 ELSE 5 –needed only is no IN clause above. eg when = ‘b’ END, id
SQL Switch/Case in ‘where’ clause
declare @locationType varchar(50); declare @locationID int; SELECT column1, column2 FROM viewWhatever WHERE @locationID = CASE @locationType WHEN ‘location’ THEN account_location WHEN ‘area’ THEN xxx_location_area WHEN ‘division’ THEN xxx_location_division END
Implement paging (skip / take) functionality with this query
In SQL Server 2012 it is very very easy SELECT col1, col2, … FROM … WHERE … ORDER BY — this is a MUST there must be ORDER BY statement — the paging comes here OFFSET 10 ROWS — skip 10 rows FETCH NEXT 10 ROWS ONLY; — take 10 rows If we want to … Read more
Reference alias (calculated in SELECT) in WHERE clause
You can’t reference an alias except in ORDER BY because SELECT is the second last clause that’s evaluated. Two workarounds: SELECT BalanceDue FROM ( SELECT (InvoiceTotal – PaymentTotal – CreditTotal) AS BalanceDue FROM Invoices ) AS x WHERE BalanceDue > 0; Or just repeat the expression: SELECT (InvoiceTotal – PaymentTotal – CreditTotal) AS BalanceDue FROM … Read more
Getting only Month and Year from SQL DATE
As well as the suggestions given already, there is one other possiblity I can infer from your question: – You still want the result to be a date – But you want to ‘discard’ the Days, Hours, etc – Leaving a year/month only date field SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, <dateField>), 0) AS [year_month_date_field] FROM <your_table> … Read more
How to use DISTINCT and ORDER BY in same SELECT statement?
The problem is that the columns used in the ORDER BY aren’t specified in the DISTINCT. To do this, you need to use an aggregate function to sort on, and use a GROUP BY to make the DISTINCT work. Try something like this: SELECT DISTINCT Category, MAX(CreationDate) FROM MonitoringJob GROUP BY Category ORDER BY MAX(CreationDate) … Read more
Is there any boolean type in Oracle databases?
Not only is the boolean datatype missing in Oracle’s SQL (not PL/SQL), but they also have no clear recommendation about what to use instead. See this thread on asktom. From recommending CHAR(1) ‘Y”https://stackoverflow.com/”N’ they switch to NUMBER(1) 0/1 when someone points out that ‘Y”https://stackoverflow.com/”N’ depends on the English language, while e.g. German programmers might use … Read more