SQL statement to remove extension......

Question

Post reply

SQL statement to remove extension......

marge0513

Mr or Mrs. 500

Points: 570
More actions
October 15, 2008 at 12:12 pm

#199383

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!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

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]

marge0513 Mr or Mrs. 500 Points: 570 More actions · Answer 2

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!!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

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]

sarahpumfrey SSC Journeyman Points: 84 More actions · Answer 4

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

Greg Snidow SSCoach Points: 16108 More actions · Answer 5

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.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

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]

Greg Snidow SSCoach Points: 16108 More actions · Answer 7

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.

karthik M One Orange Chip Points: 29139 More actions · Answer 8

Hey,

Why can't you use the below one ?

http://forums.oracle.com/

karthik

sarahpumfrey SSC Journeyman Points: 84 More actions · Answer 9

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.

Greg Snidow SSCoach Points: 16108 More actions · Answer 10

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.

SQL statement to remove extension......

Cookies on SQLServerCentral