Remove Last N Characters From Column

Posted on By David Kittell

Recently in a project to list all the plugins in a WordPress site with their versions I had accidently added .0 on to various rows in error.

This script was very helpful to correct this error

SELECT [id],
	[siteid],
	[pluginid],
	[versioninstalled],
	LEFT([versioninstalled], LEN([versioninstalled]) - 2),
	[Activated]
FROM [Documentation].[dbo].[tblWP_SiteToPlugin]
WHERE RIGHT([versioninstalled], 2) = '.0'

UPDATE [Documentation].[dbo].[tblWP_SiteToPlugin]
SET [versioninstalled] = LEFT([versioninstalled], LEN([versioninstalled]) - 2)
WHERE RIGHT([versioninstalled], 2) = '.0'

Reference: http://stackoverflow.com/questions/10470471/delete-last-n-characters-from-field-in-a-sql-server-database

All information on this site is shared with the intention to help. Before any source code or program is ran on a production (non-development) system it is suggested you test it and fully understand what it is doing not just what it appears it is doing. I accept no responsibility for any damage you may do with this code.
Code MSSQL SQL

Related Posts

MS SQL – Count Users In User Role

Posted on

SELECT UR.[Id] ,UR.[Name] ,COUNT(ur.name) FROM [dbo].[UserRole] UR INNER JOIN dbo.[User] U ON U.Role = UR.Id GROUP BY UR.Id, ur.Name All information on this site is shared with the intention to help. Before any source code or program is ran on a production (non-development) system it is suggested you test it…

Read More

Monday Date Calculation

Posted on

— SQL user-defined function — SQL scalar function – UDF CREATE FUNCTION fnMondayDate ( @Year INT, @Month INT, @MondayOrdinal INT ) RETURNS DATETIME AS BEGIN DECLARE @FirstDayOfMonth CHAR(10), @SeedDate CHAR(10) SET @FirstDayOfMonth = convert(VARCHAR, @Year) + ‘-‘ + convert(VARCHAR, @Month) + ‘-01’ SET @SeedDate = ‘1900-01-01’ RETURN DATEADD(DD, DATEDIFF(DD, @SeedDate,…

Read More

Ektron – DMS Content List

Posted on

<%@ Page Title="" Language="C#" CodeFile="DMSFileList.aspx.cs" Inherits="DMSFileList" %> <!DOCTYPE html> <html lang="en" xmlns="http://www.w3.org/1999/xhtml"> <head id="Head1" runat="server"> <meta charset="utf-8" /> <title>DMS File List</title> </head> <body> <form id="form1" runat="server"> <asp:Repeater ID="subContentRepeater" runat="server"> <HeaderTemplate> <table style="border: 1px solid #000000;"> <tr> <th>ID</th> <th>Type</th> <th>QuickLink</th> <th>Title</th> <th>Summary</th> </tr> </HeaderTemplate> <ItemTemplate> <tr> <td style="vertical-align: text-top;"> <%# DataBinder.Eval(Container.DataItem,…

Read More