October 15, 2008 at 12:12 pm
Hello, there probably is no easy way to do this, but I'll ask anyway.......
I have a file name field in my database that stores each file name with the extension .TXT and almost each file name is different.
I would like to remove this extension from all of the file names without using the different file name each time I update. Is there any SQL statement that will allow me to do this? I am using Oracle.
Thanks!
October 15, 2008 at 1:38 pm
Something like this. Test it first!
Update YourTable
Set FileName = Left(FileName, Len(FileName)-4)
Where FileName LIKE '%.TXT'
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 16, 2008 at 7:32 am
Thank you so much! I had to find out what Oracle liked because it did not like the function "Left" so I had to use this:
UPDATE TABLE
SET FileName = SUBSTR(FileName,1,LENGTH(FileName) - 4)
Thanks for leading me in the right direction. It saved me ALOT of time!!
October 16, 2008 at 8:07 am
Glad I could help!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 28, 2008 at 6:02 am
I wondered if you could tell me why this command isn't working:
UPDATE TABLENAME SET SUBSTR(FIELDNAME,1,7) = 'XXXXXXX' WHERE SUBSTR(FIELDNAME,1,7) = 'YYYYYYY';
The error I get is:
ERROR at line 1:
ORA-00927: missing equal sign
October 28, 2008 at 6:41 am
I don't believe you can update a substring of a field in place. What you will have to do is add the portion you want to keep to the portion you want to replace, like this...
UPDATE TABLENAME SET FIELDNAME = 'XXXXXXX' || SUBSTR(FIELDNAME,8,LENGTH(FIELDNAME)-7)
WHERE SUBSTR(FIELDNAME,1,7) = 'YYYYYYY';
Also, this forum is for SQL server and TSQL, not PSQL. I'm not saying that folks here will not try to help you, but you might find more help on an Oracle forum. HTH.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 28, 2008 at 7:05 am
sarahpumfrey (10/28/2008)
I wondered if you could tell me why this command isn't working:UPDATE TABLENAME SET SUBSTR(FIELDNAME,1,7) = 'XXXXXXX' WHERE SUBSTR(FIELDNAME,1,7) = 'YYYYYYY';
Substring is, like almost all functions and operators in SQL, a Right-Hand Side (or RHS) only function, meaning that it is only valid in the source (RHS) of an assignment and not in the target (or LHS). The only string function that I can think of that is valid in the target of an assignment is STUFF(), which may fit your bill.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 28, 2008 at 7:20 am
rbarryyoung (10/28/2008)
The only string function that I can think of that is valid in the target of an assignment is STUFF(), which may fit your bill.
The OP is using Oracle, so STUFF() is not an option to her.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 28, 2008 at 7:22 am
October 28, 2008 at 8:40 am
OK - I will try another forum - I only stumbled across this one yesterday when I was trying to find a solution! Thanks everyone for their suggestions anyway.
October 28, 2008 at 10:04 am
Good luck, and note that my solution above will work for you, as I tested it on an Oracle server. In Oracle, || replaces + in concatanation.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply