Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 19th, 2006, 03:34 PM
Authorized User
 
Join Date: Jun 2004
Location: York, East Yorkshire, United Kingdom.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL REPLACE after a character.

Hi, Does anyone know of a way to replace everything in a field after a certain character.

For example:
iwantthistext/butidontwantthis1/orthiseither1/
iwantthistext/butidontwantthis2/orthiseither2/
iwantthistext/butidontwantthis3/orthiseither3/
iwantthistext/butidontwantthis2/thistextisalwaysdifferent/
iwantthistext/soisthistext/orthiseither5/

Anyhelp or advice greatfully, as always received.

Thanks in advance

Stuart (chief pain)
Reply With Quote
  #2 (permalink)  
Old March 20th, 2006, 07:21 AM
Registered User
 
Join Date: Mar 2006
Location: CHennai, Tamil nadu, India.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

There is no available function to do this in SQL.YOu have to write a logic to loop through and check for the character you are refering to.

Regards
venkat


Reply With Quote
  #3 (permalink)  
Old March 22nd, 2006, 09:53 AM
Authorized User
 
Join Date: Mar 2006
Location: , , .
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi!

Venkat is right, there is no specific function to do this. But, we can try a way to accomplish this.

[1] Let us assume that you have a table emps that is created something like this -

Create table emps ( id identity(1,1) , name varchar(20))

You can alter the table and add the identity column.

[2] Insert the same entry in all the rows of the table using -

Insert into emps values ('Stuart Stalker')

id will automatically updated like counter, you need not enter it. Repeat the above statement as many time you like.

[3] Now, if you want to select only from the table. Use Select. I am using Update statement in my example.

UPDATE emps set name=substring(name,1,6)+cast(id as char(1))+substring(name,8,20)+cast(id+1 as char(1))

This statement will Update your table permanently. So, instead of that you can do this -

SELECT name=substring(name,1,6)+cast(id as char(1))+substring(name,8,20)+cast(id+1 as char(1))
from emps

This will produce output like this -

 name
 ----------------------------
 Stuart1Stalker2
 Stuart2Stalker3
 Stuart3Stalker4
 Stuart4Stalker5
 Stuart5Stalker6

The result is based upon my assumption. Your case may be different. I can not recommend this. But, you can try this if this is so urgent to do. Tell us clear scenario of table, if you are do not get any solution.

Reply when you read this.

Reply With Quote
  #4 (permalink)  
Old March 29th, 2006, 08:07 AM
Registered User
 
Join Date: Mar 2006
Location: Kolkata, West Bengal, India.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Papak
Default

Hi Stuart,
           I am really not clear about your problem. Please tell me clearly if I can help you.

Thanks
Papak
Reply With Quote
  #5 (permalink)  
Old March 29th, 2006, 08:11 AM
Registered User
 
Join Date: Mar 2006
Location: Kolkata, West Bengal, India.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Papak
Default

Why don't you use
Select Replace ('abdc','a','ax')
will return you : axbdc

Thanks
Papak
Reply With Quote
  #6 (permalink)  
Old March 29th, 2006, 03:02 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

Maybe this is what you are looking for. Run this is QA and see if that returns what you want.

Declare @theLeftLength int, @theTotalString varchar(30)
Set @theTotalString = 'iwantthistext/butidontwantthis2/thistextisalwaysdifferent/'
set @theLeftLength = charindex('/',@theTotalString)

Select left(@theTotalString,@theLeftLength - 1) as 'The Wanted String'


Thanks,
Richard


Reply With Quote
  #7 (permalink)  
Old April 11th, 2006, 02:13 PM
Authorized User
 
Join Date: Jun 2004
Location: York, East Yorkshire, United Kingdom.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

since posting my question and receiving those kind and helpful responses I found this article: http://www.informit.com/articles/art...p?p=25040&rl=1 so have posted for the next puzzled guy (or girlie)

Thank you everyone for your time

Stuart
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
XSLT replace for character entities atulshin XSLT 1 November 1st, 2008 05:19 AM
Replace character gregalb SQL Server 2000 6 July 12th, 2007 01:14 AM
Character replace function? echovue Access 2 December 21st, 2004 01:53 PM
Replace Character " fosca XSLT 1 November 11th, 2004 04:14 PM



All times are GMT -4. The time now is 12:27 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.