I have a table (named profile) in my Postgres database, which has 3 columns: ID, Day, Ftfm_profile of type jsonb, I tried to extract the row where the profile name (ftfm_profile->’name’) begins with ‘LFBB’ ( sql: LFBB%) using the wildcard as following: the expected result: I can’t seem to find the solution, thanks for your help Answer One option unnests
Tag: string
How to deal with Case Sensitivity in PostgreSQL
I have a master table for color codes which stores various unique color values. It has a column Color_value on which UNIQUE constraint has been applied. However it is accepting both ‘Black’ and ‘black’ values. How can such situations be handled? Answer You can create a unique index like this: That said, it would be much simpler to make your
How can I use WHERE with multiple different columns?
Having trouble trying to query AND and OR in an SQlite3 statement. Is there a way where I can do something like WHERE section = ? AND LEVEL =? AND subj1 =? AND subj2=? AND subj3 =? From what I see in this particular line It won’t work because after ‘AND subj1 =?’ SQlite3 now chooses to select whatever subject
Get rows which contain exactly one special character
I have a SQL query which returns some rows having the below format: How can i filter to get rows which only have the format of ‘DB_host’ (place a condition to return values with only one occurrence of ‘_’) i tried using [0-9a-zA-Z_0-9a-zA-Z], but seems like its not right. Please suggest. Answer One option would be using REGEXP_COUNT and at
Eliminate double quotes from a json field and deal with characters in PostgreSQL
Some time ago I needed help with a postgresql database with a jsonb field to convert numerical values surrounded by double quotes into standard numerical values and this solution worked very well: Eliminate double quotes from a json field in a selective manner in PostgreSQL I had a field named “test_json” which is composed of for example these 2 rows:
SQLite Incomplete Time String
I have a table with incomplete time strings, such as ‘0:09.735’ (minutes:seconds.milliseconds). As the function strftime expects certain formats, such as ‘%H:%M:%f’, I have “to complete” the incomplete timestring first. The first strftime function with the complete timestring shows the correct result, the second, third, and fourth one with the incomplete timestring do not. Is there a flexible way that
How to convert char data type to date in SQL Server?
In a table, there is a column BeginDate of type char(10) and all the records are saved in 2020/05/06 format. How can I change the data type to Date? Answer You could strip the / characters and then you have the ISO format yyyyMMdd.: But the real solution is fix your design; stop storing dates as a varchar.
SQL Merge the row into new column
I have a table named Addresses. The columns are: There is no data in Property as of yet as I will update it manually later. The rest of the columns does hold data though. However I need to merge Road, Town, Borough, District and Postcode into a new column named Full Address. So for example If you can see the
SQL new table with conditions
I have a table and I want through sql to parse it into another table. The problem is that there is a condition: There are 3 columns in old version And I want to store them into new table’s column like : Thanks in advance. Answer You can use case expressions and string concatenation, like so: This uses standard string
How to retrieve the last part (i.e. a number varies in length) of a text column of which’s length varies in Oracle and store this in another column?
I am trying to get the last number from a Oracle SQL column called Description and store it in another column called Thickness. As you see, the length of the description varies as well as the length of the number at the very end. It is sometimes a double such as 1.5, sometimes an integer like 3. I have tried