The  "CASE" statements above look correct, but that method will always add the commas even if there is no displayed value.  You may get a final string that looks like...

Rather then convert to numbers, transform, and convert back, you could just select the portion of the string you want.

SELECTMyColumn,
NewColumn= SUBSTRING(MyColumn, 1, CHARINDEX('.', MyColumn) + 1) 
/*...

Thank you for providing the table definitions, data, and code to produce the results.  However, executing the code gives the following error: Invalid object name 'EmployeeMemberMaster'.  That table needs to...

• This reply was modified 4 years, 10 months ago by  fahey.jonathan. Reason: Changed ROW_NUMBER sort to DESC to show the largest 4, not the smallest 4

This looks like a great place to use the EXCEPT statement.  EXCEPT is basically the opposite of UNION; UNION joins two sets of data, while EXCEPT subtracts one set from...

I believe it has to do with collation, based on his article: https://dba.stackexchange.com/questions/208414/why-isnt-unicode-character-replaced-in-some-cases.

SELECTSampleWord,
[No_Collate]= CAST(REPLACE(REPLACE(SampleWord, NCHAR(0x0D), ''), NCHAR(0x0A), '') AS NVARCHAR(50)),
[With_Collate]= CAST(REPLACE(REPLACE(SampleWord COLLATE Latin1_General_100_BIN2, NCHAR(0x0D), ''),...

Jeff: Thanks for pointing out dbgaragedays's post; I skipped right over it.

Rather than check the table, I check the main index on the table to see how many rows it has.  In my experience, this is much faster than checking the...

This gives a running total, ordered by the foo_id. You can now take this running total and put it in a view that shows (running_item_tot % 6). I'll let...

I usually solve the problem by doing a JOIN first to get the set of records with a "SortSeq" column that calculates "1" for my preferred row, the JOIN that...

• This reply was modified 5 years, 4 months ago by  fahey.jonathan.

I find it humorous that, for someone who argued so strongly against Hungarian notation on another thread, this object names starts with "fn".  🙂

Question: rather than have one function that...

Typing in all that data individually could be cumbersome.  There are system stored procedures to help with maintenance, such as sp_AddExtendedProperty.  Using these procedures, we can integrate the maintenance...

Building on DesNorton's statement, you may want to show all the dates in the range, even if those dates have no sales.  To do that, you need to build a...

Thank for providing a scenario.  To follow on, a procedure that lives on DatabaseA and references DatabaseA and DatabaseB, which are restored as DatabaseA_20190702 and DatabaseB_20190702, would have a similar...

If this is within a stored proc in the database, please consider removing the EtiquetasKLC. prefix from the table names. This is a bad practice, unless you are executing...

Here is another option to add to your list.

WITHBaseData
AS(
SELECTmemid,
memtype,
mdate,
ROW_NUMBER() OVER (PARTITION BY memid ORDER BY mdate) AS RowAsc,
ROW_NUMBER() OVER (PARTITION BY memid ORDER BY mdate DESC) AS...