Today in this post we will discuss about How to update path in table column using SQL
When we need to do this
Sometimes, we save path of any file in database, assume that all files being saved in D drive but later you don’t want to save your files on D drive, maybe you need to switch your host and their D drive not available. There might be many cases when we need to change the path.
Now question is how we can easily change the file path using sql command, its very simple I will show you how.
In my query I am updating path from D drive to G drive, you can assume that the paths are saved in table all belongs to the drive D. Now we will update all our paths by using simple sql query.
Update TableName set FilePath = 'G' + SUBSTRING (LTRIM(FilePath), 2, LEN(FilePath)), Where FilePath like 'D%'
In above sql query I am using ‘SUBSTRING’ function of MSSQL server, which takes three param, first param where to find the string, second one is Index like from where you want to get the text like in above example I am getting value from second index not first and third parameter tells count to sql means how many chars need to include after starting from index.
Things to remember
Normally index starts from 0 but in sql it will start from 1, so adjust your query accordingly and you may also noticed that why I am using LTRIM what is the use of this sql function?
Sometime accidentally we inserts some empty spaces why programming language or manually insertion. You should check the paths carefully if your path contains empty spaces then you should use LTRIM sql method this function is used to remove spaces from the string
After that you might also noticed that we are only getting those records where path starting from letter D, we don’t need any other records we only want to change the path of drive D.
Hope you will like this, please forget to share your thoughts
