You are probably looking for the replace function. For example, update table_name set content_url = replace(content_url, ‘band%20albums’, ‘bands/studio%20albums’) where content_url like ‘%nazgulled/music/band_20albums/%’; More documentation at http://sqlite.org/lang_corefunc.html
Try using CAST(columnName AS INT) AS IntValue. e.g. SELECT columnName, CAST(columnName AS INT) AS IntValue FROM table OR you can use CONVERT(INT, columnName) AS IntValue. UPDATE: If you need to alter the actual metadata of the table, then you first need to drop the constraints then alter the column: i.e. ALTER TABLE [Table] DROP CONSTRAINT … Read more
You need to use the whole CASE statement in the GROUP BY clause if you don’t wrapped it in a subquery. SELECT CASE WHEN DATEDIFF(o.EndDate, o.StartDate) < 30 THEN ‘<1 Month’ WHEN DATEDIFF(o.EndDate, o.StartDate) < 90 THEN ‘1 – 2 Months’ WHEN DATEDIFF(o.EndDate, o.StartDate) < 210 THEN ‘3 – 4 Months’ ELSE ‘>4 Months’ END … Read more
While the following addresses multiple null values, it does not address any “issues” associated with such a design, other than possible database/SQL portability – as such, it should probably not be considered an answer, and is left here merely for reference. This is actually covered in the SQLite FAQ. It is a design choice – … Read more
Use jsonb_array_elements() in a lateral join as many times as the depth of a json array which elements you want to compare: select schools->>’school_id’ school_id, addresses->>’addr_id’ addr_id, addresses->>’house_description’ house_description, addresses->>’house_no’ house_no from title_register_data, jsonb_array_elements(address_data->’schools’) schools, jsonb_array_elements(schools->’addresses’) addresses where addresses->>’house_description’ = addresses->>’house_no’; school_id | addr_id | house_description | house_no ———–+———+——————-+———- 1 | 4 | 1 | … Read more
This happens because the collations on #tempdb.temp_po.OD1 and STR_IndentDetail.D1 are different (and specifically, note that #tempdb is a different, system database, which is generally why it will have a default opinion for collation, unlike your own databases and tables where you may have provided more specific opinions). Since you have control over the creation of … Read more
If you’re trying to capture the regex match that resulted from the expression, then substring would do the trick: select substring (‘I have a dog’, ‘d[aeiou]g’) Would return any match, in this case “dog.” I think the missing link of what you were trying above was that you need to put the expression you want … Read more
Identifiers need to be quoted with double quotes (“). Single quotes (‘) denote a character value (not a “name”). Therefor you need to use: SUM(part_gold) as “Number of Gold Medals” More details in the manual: Database Object Names and Qualifiers Text literals
GETUTCDATE() represents the current UTC time (Universal Time Coordinate or Greenwich Mean Time) It is a nondeterministic function, Views and expressions that reference this column cannot be indexed. The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server … Read more
Yes, you can join sub queries. Like this: var query = from f in db.ApplicationFees join sub in (from p in db.Payments where p.Type == 1 && p.Position == 1 && p.Date >= fromDate && p.Date <= toDate group p by p.ApplicationNo into g select new { ApplicationNo = g.Key, CNT = g.Count(), AMNT = … Read more