Previous Thread
Next Thread
Print Thread
Hop To
Joined: Jan 2009
Posts: 92
whk
Offline
Journeyman
Journeyman
Joined: Jan 2009
Posts: 92
I have defined Custom Field 1 to hold the URL for an external database record. The database is powered by ColdFusion and we're changing the server it is hosted on. The data stored in USER_EXTRA_FIELD_1 is either blank or has a fully qualified URL in the format of http://domain_name.org/updtform1.cfm?id=375

The number at the end of the URL is a unique record pointer and can be anything from 1 to 999.

What I would like to do is run a SQL script that will allow me to strip out everything but the number at the end of the URL and save it back to USER_EXTRA_FIELD_1 as just the number in text form. If USER_EXTRA_FIELD_1 was blank, it just ignores the record. That way I can concatenate the new URL and the stored number without having to store the entire new URL.

Can anyone help me with the SQL script?

Joined: Apr 2007
Posts: 3,940
Likes: 1
SD Offline
Former Developer
Former Developer
Joined: Apr 2007
Posts: 3,940
Likes: 1
SQL Query
update ubbt_USER_PROFILE
SET USER_EXTRA_FIELD_1 = REPLACE(USER_EXTRA_FIELD_1, 'http://domain_name.org/updtform1.cfm?id=', '')

assumes the same thing is up front for every one and that the only difference is AFTER the = sign

Joined: Jan 2009
Posts: 92
whk
Offline
Journeyman
Journeyman
Joined: Jan 2009
Posts: 92
Thanks! That is correct on the assumption too.

Joined: Jun 2006
Posts: 16,292
Likes: 116
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,292
Likes: 116
Though if that site allows users from a www and a non-www domain, you may end up with some users pasting the other url wink


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum, Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Joined: Jan 2009
Posts: 92
whk
Offline
Journeyman
Journeyman
Joined: Jan 2009
Posts: 92
actually I'm the one who loads that field for everyone and I know better smirk

It is displayed as a hot link that is only visible by admins and the specific user logged in.

This just saved me a lot of editing.


Link Copied to Clipboard
ShoutChat
Comment Guidelines: Do post respectful and insightful comments. Don't flame, hate, spam.
Recent Topics
spam issues
by ECNet - 03/19/2024 11:45 PM
Looking for a forum
by azr - 03/15/2024 11:26 PM
Editing Links in Post
by Outdoorking - 03/15/2024 9:31 AM
Question on barkrowler and the like
by Mors - 02/29/2024 6:51 PM
Member Permissions Help
by domspeak - 02/27/2024 6:31 PM
Who's Online Now
0 members (), 744 guests, and 147 robots.
Key: Admin, Global Mod, Mod
Random Gallery Image
Latest Gallery Images
Los Angeles
Los Angeles
by isaac, August 6
3D Creations
3D Creations
by JAISP, December 30
Artistic structures
Artistic structures
by isaac, August 29
Stones
Stones
by isaac, August 19
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20230217)