x

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 '11 at 09:00 AM in Default

winnie2K gravatar image

winnie2K
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 '11 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 '11 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 '11 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)

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

-----------------------------------
John Doe ?z1234563 Sally D #3442233
more ▼

answered Aug 15 '11 at 03:14 AM

Phil Factor gravatar image

Phil Factor
3.8k 8 9 16

Phil Factor, thank you!!! This works great.
Aug 15 '11 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:

SELECT
    AField,
    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 '11 at 12:12 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x6

asked: Aug 12 '11 at 09:00 AM

Seen: 683 times

Last Updated: Aug 12 '11 at 09:01 AM