I have a text memo field in SQL table that I need to remove the last character in the field if it’s a comma. So, for example, if I have these rows, I need to remove the commas from rows 2 and 4. The output would be: Any ideas? Answer Using REVERSE and STUFF: First, you want to TRIM your
Tag: sql-server
SQL Server : ISNULL(compound NULL condition, ‘a string’) returns only the 1st character, under certain circumstance(s)
I’m a self-taught, vaguely competent SQL user. For a view that I’m writing, I’m trying to develop a ‘conditional LEFT’ string-splitting command (presumably later to be joined by a ‘conditional RIGHT’ – whereby: If a string (let’s call it ‘haystack…
SQL query to get Stored Procedure crop result if the routine_definition is greater than 4000 char
I use this query to get all the stored procedure in my database (I’m using Microsoft SQL Server 2008): For almost all result everything is ok, but for the row with a very long ROUTINE_DEFINITION the result is cropped. Do you know how to solve it? Answer Please try with sp_helptext ‘ProcedureName&#…
C# sql create one connection and open and close for each query
I recently inherited a C# Web app that creates a new connection for every query like so: I understand that this is typically the best way to do it, but is it acceptable or “best practice” to have the constructor create the connection object, and then have each method/Query open and then close that…
How to retrieve count of records in SELECT statement
I am trying to retrieve the right count of records to mitigate an issue I am having. The below query returns 327 records from my database: Where I run into trouble is trying to reconcile that with the below query. One is for showing just a count of all the particular students the other is showing pertinent in…
drop trigger if exists and create
I would like to check if the trigger exists on [tbl] and create another one. I tried it this way but didn’t work. What am I doing wrong? Answer The [name] field in sys.objects will contain only the actual name (i.e. trg), not including the schema (i.e. dbo in this case) or any text qualifiers (i.e. [ an…
Grouping Totals With SQL Query
Here’s the situation: I have a table that has two data columns: And so on… I would like to create a query that presents the totals for the different numbers separately, so it would end up being like so: I’ve tried applying a simple SUM(Value) into my Query, but I can’t seem to get it r…
A constant expression was encountered in the ORDER BY list
Below is my dynamic query and it’s not working. It threw: A constant expression was encountered in the ORDER BY list, position 2. Original dynamic query: Extracted Query: If I remove second order by line, CASE WHEN ‘ItemDescription’=’ItemDescription’ AND ‘0’= ‘1…
Save datetime in sql table
How I can insert with a query a date like this? 2015-06-02T11:18:25.000 I have tried this: But I have returned: I tried also: but it is not working: The entire query is: What is wrong? Answer Try this: 126 relates to ISO8601, which is the format yyyy-mm-ddThh:mi:ss.mmm. This is the same format as the string &…
RANDBETWEEN for SQL Server 2012
How would you create a function that returns a random number between two number? example syntax RandBetween(3,300) Answer How about Use RAND() (which returns a value between 0 and 1 (exclusive). multiply by 298 (since you want a dynamic range of [300-3] = 297 + 1) add 3 to Offset and cast to INT? i.e. Fiddle …