Extracting character embedded in a string

Hi all, I want to know if anyone could help me on a string issue I’m having. I get data from a mainframe that is returning a string with some special characters in it. See below

John Doe ?z1234563? Sally D ?1053442233?

1.) I want to remove the second “?” after John Doe
2.) I also want to replace ?105 after Sally D with a “#” sign and leave 3442233 and remove the last “?”.

Can someone help me on this? I’m having a very hard time.

Thanks, Ben
more ▼

asked Aug 12, 2011 at 09:00 AM in Default

winnie2K gravatar image

11 1 1 1

Can you provide the query you have put together so far? Do you need to do this on every record that is returned?
Aug 12, 2011 at 09:33 AM Shawn_Melton
Let me see if I understand your requirements. You have a column which will contain a string on the format Name ?somecode? Name ?1234567890? and you want it on the format Name ?somecode Name #4567890?. Is that correct?
Aug 12, 2011 at 09:50 AM Magnus Ahlkvist

I need to return on all records that meet this pattern.

In the example it's one string John Doe ?z1234563? Sally D ?1053442233?
Aug 12, 2011 at 10:52 AM winnie2K
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

On the face of it, the following code will do what you want, but I'm wondering whether the codes can be any length and you'd like them truncated from the left to seven characters. I'm having some difficulty in working out more exactly what the problem is that you're wanting to solve. (this code doesn't exclude those lines that don't have eight consecutive numbers after the '?'. you'd do a LIKE with the same wildcard)

  Stuff ( Clipped, PatIndex( '%?[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', Clipped ), 4,
         '#' )
           rTrim( REPLACE( 'John Doe ?z1234563? Sally D ?1053442233?' + ' ', '? ', ' ' ) )
           Clipped )FirstPass 

John Doe ?z1234563 Sally D #3442233
more ▼

answered Aug 15, 2011 at 03:14 AM

Phil Factor gravatar image

Phil Factor
3.9k 8 9 16

Phil Factor, thank you!!! This works great.
Aug 15, 2011 at 06:54 AM winnie2K
(comments are locked)
10|1200 characters needed characters left

You can do any pattern matching and replacement easy by CLR RegEx implementation.

You can take a look on the sample implementation [SQL Server Regular Expressions – Replacements][1].

If I understand it exactly as @Magnus Ahlkvist in the comment, then using the above implementation, you can easily run below code:

    dbo.fn_RegExReplace(AField, '(.+?)(\?)(.+?)(\?)(.+?)(\?105)(.+?)(\?)', '$1$2$3$5#$7', 0) AS UpdatedField
FROM aTable

If you need other results, it's only a question of passing right Regular Expressions.

[1]: http://www.pawlowski.cz/2011/05/sql-server-regular-expressions-replacements/
more ▼

answered Aug 12, 2011 at 12:12 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 12, 2011 at 09:00 AM

Seen: 824 times

Last Updated: Aug 12, 2011 at 09:01 AM