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

Show Rows That Would Change
SELECT [id],
    [siteid],
    [pluginid],
    [versioninstalled],
    LEFT([versioninstalled], LEN([versioninstalled]) - 2),
    [Activated]
FROM [Documentation].[dbo].[tblWP_SiteToPlugin]
WHERE RIGHT([versioninstalled], 2) = '.0'
Update Rows
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

Windows Phone 8/8.1 – My Location

Posted on

If you have Windows Phone and would like to be able easily send your location to someone the code below will be helpful. When reviewing the code you will find that it is functional but lacks a pleasant user interface, as you build this app you can add your own…

Read More

Change Computer Name

Posted on

Option Explicit Set ws = WScript.CreateObject("WScript.Shell") Dim ws, t, p1, p2, n, cn, vbdefaultbutton Dim itemtype p1 = "HKLMSYSTEMCurrentControlSetControlComputerNameComputerName" n = ws.RegRead(p1 & "ComputerName") t = "Change Computer Name" cn = InputBox("Type new Name and click OK", t, n) If cn <> "" Then ws.RegWrite p1 & "ComputerName", cn End…

Read More

FormatDateTime Function

Posted on

This is a default function within VBScript that should work in any VBScript situation. FormatDateTime(date,format) Date is required and has to be in a valid format but easily can accept date() or now() Date() would only display the current date where now() would be date and time. Format would be…

Read More