Skip to main content

SQL Server - Trim All Trailing "/" in a Table Column

Problem:

 Some of our redirects in our main web application were acting a little screwey. Turns out our stand-alone URL redirect app didn't play well with URLs with a trailing "/".  I needed to simply trim all trailing "/" from URLs that have them.

 Solution:

 Here's the script I created:

BEGIN TRANSACTION

       SELECT [url] AS 'url with trailing "/"'
       FROM   [URLRedirects].[dbo].[Redirects]
       WHERE  RIGHT([url], 1) = '/'

       UPDATE [Redirects]
       SET    [url] = CASE RIGHT([url], 1)
                                         WHEN '/' THEN LEFT([url], Len([url]) - 1)
                                         ELSE [url]
                                     END
       WHERE  RIGHT([url], 1) = '/'

       SELECT 'Updated ' + CONVERT(VARCHAR, @@ROWCOUNT) + ' records'


       SELECT CONVERT(VARCHAR, Count([url]))
                 + ' records with trailing "/" after update'
       FROM   [URLRedirects].[dbo].[Redirects]
       WHERE  RIGHT([url], 1) = '/'

       SELECT [url]
       FROM   [URLRedirects].[dbo].[Redirects]


ROLLBACK TRANSACTION

The meat and potatoes is the highlighted update + case statement.  I also added some selects to see the results of the execution.  I naturally wrap my statements in a transaction and ROLLBACK in case bad things happen.  Once you're confident executing, change ROLLBACK to COMMIT.

 Results:

 This is a simple output of the results just to see if it affected anything.


Hope this helps somebody.

Comments

Post a Comment

Popular posts from this blog

Resolved Sitecore "If you publish now, the selected version will not be visible on the web site" warning

The Problem:   Unable to publish any Sitecore item within a particular site, even out of the workflow. Rather, a warning reads " If you publish now, the selected version will not be visible on the web site " What I've Discovered: I couldn't publish any item in the site, not just one or two I viewed all parent items of the items in question The main home page displayed a different warning " This item will never be published because it's publishable option is disabled " Another sitecore developer reminded me of the standard fields option View --> check standard fields to show all standard page fields Found out that somebody checked Never Publish within the publishing section The Fix: After I unchecked the checkbox and saved the change, I was able to publish again.  :) Conclusion: Looks like another Sitecore user thought the children items would not be affected by this change.  Lesson  learned.
Post a Comment
Read more

[Resolved] Sitecore ParseException: End of string expected at position...

Problem:  I have a line of code that uses Sitecore Fast Query to pull all items + children starting with a site item, like so: Item [] allItems = db.SelectItems( "fast:" + sitecorePath + "//*" ); Unfortunately, I would get a Sitecore parsing error at runtime: ParseException: End of string expected at position... Turns out Sitecore doesn't like hyphens ('-') in any sitecore path when using fast query, which I have a few distributor sites in a folder which contained hyphens. Solution: I create a simple method that resolves a sitecore path to be Sitecore fast query friendly:             string sitecorePath = "" ;             if (siteItem.Paths.FullPath.Contains( "-" ))             {                 String [...
Post a Comment
Read more

Create a File Upload Service using Node.js / Express / Multer

Problem:    I needed to create a form that essentially uploads a file to a server, then posts the rest of the form data to a second server with the new file name. In other words, post the image to an “Image” server, then posts the form data (with the file URL) to a “Form Data” server. Solution:  Create an Express server that uses the “Multer” library to accomplish this. The Code: Create the express server and have it listen on a specific port var express = require( 'express' ); var app = express (); app . listen ( 3313 , function (){     console . log ( 'listening on port 3313' ); }); Install Multer.  Read here: https://www.npmjs.com/package/multer Include Multer to your express app.  We’ll be tweaking the storage properties so you can name the file whatever you like. At the end, you should have an “upload” object you’ll be using in the next step. be sure to change the file destination of your choice. var expre...
6 comments
Read more