Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Change part of a parth to lowercase in SQL script

Author  Topic 

Addcom
Starting Member

5 Posts

Posted - 2014-06-27 : 02:35:54
I instance where customers want to migrate SQl dB to a new server. I want to change part of the path to lowercase. the part to be change is always a computer name i.e. \\Computername\Data\report\A24440 testUser.doc I want to define the \\computername and the script changes only the computername to lowercase
Using
Update Table
set Path =LOWER(Path) -- etc.
Go
Changes all the path to lowercase,
Note the Computername will always be different depending on the customer. The reason for changing the PCname to lowercase each client can type the PC name in various ways
i.e. Texdom, servicedom, XDB-XDE-46
Any help is welcome, Thanks

albert

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-27 : 14:33:22
update yourtable
set path = lower(left(path, charindex('\', substring(path, 3, 100)) + 1)) + substring(path, charindex('\', substring(path, 3, 100)) + 2, 100)

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Addcom
Starting Member

5 Posts

Posted - 2014-06-28 : 04:58:28
Thanks Tkizer, it worked, I appreciate your quick response. As am new to SQL, can you explain how it works.
Thanks again

albert
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-30 : 12:34:39
CHARINDEX finds the position of a character, SUBSTRING gets a partial string starting at a specified position, LEFT gets the left n characters, and LOWER switches everything to lower case.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Addcom
Starting Member

5 Posts

Posted - 2014-06-30 : 19:10:58
Thanks for the info, well apreciated

albert
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-30 : 19:18:33


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -