Updating a column using %

Posted on 2011-10-31
Last Modified: 2012-05-12

I have a table called MyTable.
I want to update comments value for the rows that ends with '[address]' and '[phone]' from the paths specified in 'directory' column.
MyTable looks like this.

ID| directory                    |Comments
1 | A\B\C\D\[address]      |Jason
2 | E\F\G\K\[[bank]         |Poland
3 | H\I\J\L\[phone]          |Tree
4 | AA\CA\BB\D\[county]  |Oregon is near!
5 | KI\Y\Z\NC\[salary]      |

I used the query like

Use master
Update MyDB.dbo.MyTable set comments = 'MyFolder'
Where directory like '%address]' or
directory like '%phone]'

This actually works fine.  However, is there any part that I need to modify my script so that it becomes more efficient/elegant code?
Question by:IzzyTwinkly
    LVL 28

    Accepted Solution

    I would have done it the same way quite frankly.
    LVL 19

    Assisted Solution

    by:Bhavesh Shah

    if [address]is coming always @ last place then you can use

    Where directory like '%address]' or  directory like '%phone]'

    Open in new window

    second way is

    Where charindex('address',directory) > 0

    Open in new window

    LVL 5

    Assisted Solution

    I think this query is already written in efficient way as you done

    Use master
    Update MyDB.dbo.MyTable set comments = 'MyFolder'
    Where directory like '%address]' or
    directory like '%phone]'

    But don't  forget to change your database from Master to your Database name when you executing script.


    LVL 50

    Assisted Solution

    using a separate column to store the "immediate" directory name separate from the rest of the path

    would allow for a more efficient processing option for the query (assuming you then index the new column) as you
    wouldn't need the wildcard and an index lookup could be performed rather than the full table scans required at present...


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo‚Ķ
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now