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

Basic Recursion Example in JavaScript

Problem :  I've been having to write solutions which required some sort of recursion.  The last recursion assignment I had to write was a homework assignment about 10 years ago. I remembered the concept, but forgot how to write it. Solution :  Here’s a simple example of recursion. I start with an array, and after I fire the function, I remove that index from the array, so it shrinks. Once the array is empty, recursion stops.  Pretty straight forward! Code : let books = [ 'Chosen by God', 'Holiness of God', 'Essential Truths', 'Justified by Faith' ]; function readBooks(b){ while (b.length > 0){ console.log('I\'v read ' + b[0]); b.shift(); readBooks(b); } } readBooks(books);
Post a Comment
Read more