How can I get all values of an NVARCHAR column whose some of the values were accidently inserted without using the N prefix and so was replaced with ?, and then change those values into the correct form? For example, if I have the following: Output: I want to get what was originally ‘иытание2’ and later turned into gibberish, and
Tag: sql-server
How to get specific part string from column
I have two tables one is SifraArtikla The second table ArtikalUsluge extend the SifraArtikla ID I need to get from PuniNaziv column ‘PW60’ if ArtikalID is 1 else if ID is 2 ‘PW120’ else if ID is 3 ‘pw250’ I tried with if else statment but it didn’t work. Can anyone give me some hints please. This is what I
Aggregate time from 15-minute interval to single hour in SQL
I have below table structure in SQL Server: Is there a way to group above data into one hour interval instead of 15 minute interval? It has to be based on start and end columns. Thanks, Answer Maybe something like the following using datepart?
SQL Server: Convert YYYYMMDD-HHMM and YYYYMMDD to mm/dd/yyyy hh:mm
I am trying to convert the time format from YYYYMMDD-HHMM and YYYYMMDD to mm/dd/yyyy hh:mm. My issue is that the data is not consistent. Below is some exemple: I tried to use: but the issue is that some data does not have time. Answer You can do it like this:
Combine like rows based on substring of one column?
I have a table of parts that looks like this: Part Part Num Thing1 Thing2 Thing3 Thing4 Door 10105322 abc abc Door 10105323 abc abc Door 10105324 abc abc Door 84625111 abc abc abc Door 84625118 abc abc abc Door 84625185 abc abc abc Door 56897101 abc abc The part number is always 8 characters. For many parts, the first
Determine if an event happened every hour for 24 straight hours
Edit – I am still digesting the great responses and learning how they work, awesome knowledge! Using SQL Server 2016, I am trying to figure out if any users have done something at least once every hour, for 24 consecutive hours. I have a table similar to this, in the example below, the only user who qualifies is ‘21482’. They
How to use ‘between’ in CASE statement?
I need to select Shift from my production table such that if the time time is between 05:00 PM to 06:30 AM it is Shift B else its Shift A. The SQL that I have written is quite simple but does not gives me the expected results. The SQL is The Result I get is Shift_FK_ID 1 1 1 Where
Convert table data and reformat
I have table named Table id Level1 Level2 Level3 1 US CA 13000 2 FR PA 30000 3 US CA 24000 4 US LA 10000 5 UK LN 500 6 UK LN 600 7 FR PA 888 8 FR DF 1000 and I would like to convert it to the (First Conversion) below format which will sum Level2 and column
Display records that are not present in a table and a given date range
Let’s say I have two tables – Student and Student Attendance tables. The Student table displays basic information about the student: Student# FirstName LastName 201710 John Smith 201711 John Doe 201712 Anna Sy 201713 Chris Dy While the Student Attendance table displays the TimeIn and TimeOut of the student from class: Student# Date TimeIn TimeOut 201710 2016-01-06 00:09:00.000 00:15:00.000 201711
How to extract numbers from a string SQL
Ts there a way to extract numbers from a string? In my database there is a column called Reference and it contains multiple numbers CM|319|14163|||109|405000 Is there a way to get the first number like this? select CM|%s|… as ParentId from table So the output should be 319 And maybe even multiple like select CM|…|%s|… as SiblingId, CM|%s|… as ParentId