Regular Expressions in Find and Replace in SSMS?

Question

Regular Expressions in Find and Replace in SSMS?

Stamey

SSCertifiable

Points: 5600
More actions
December 2, 2010 at 12:08 pm

#232077

I have a table def I need to modify. I thought to myself, it sure would be nice if Find and Replace could simplify this.
Table Def example:
[PharmacyCity][varchar](25),substring([RawData],(378),(25)),(0))),
[PharmacyState][varchar](2),substring([RawData],(403),(2)),(0))),
[PharmacyZipCode][varchar](9),substring([RawData],(405),(9)),(0))),
[DrugNumber][varchar](11),substring([RawData],(414),(11)),(0))),
[DrugName][varchar](33),substring([RawData],(425),(33)),(0))),
[SubmittedQty][varchar](6),substring([RawData],(458),(6)),(0))),
[PAReasonCode][varchar](3),substring([RawData],(464),(3)),(0))),
[PAStatusCode][varchar](1),substring([RawData],(467),(1)),(0))),
[PANumber][varchar](14),substring([RawData],(468),(14)),(0))),
[PATCN][varchar](30),substring([RawData],(482),(30)),(0))),
[PADate][varchar](8),substring([RawData],(512),(8)),(0)))
What I need to do is remove everything from the " substring" to the end of the line, for each line.
So I noticed wildcards in the Replace function, as well as Regular expressions.
I have tried various things, and the Help for this is rudimentary at best.
This doesn't work, even though Help says that the ":z" is a wildcard for any integer.
substring([RawData],(:z),(:z)),(:z))),
substring([RawData],(:z:z:z),(:z)),(:z))),
I tried a few other things as wildcards and regular expressions but SSMS didn't like any of them.
In this case, the table def I need to modify is only 30 fields, but I was hoping to learn this so maybe it could help out with a bigger job in the future.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.

Viewing 5 posts - 1 through 4 (of 4 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Can you just use a LEFT() expression on each row, up to the first comma?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Stamey SSCertifiable Points: 5600 More actions · Answer 2

I don't think that SSMS will recognize that. I don't think that Left() is a regular expression operator.

Keep in mind that I am not talking about SQL Server functions here. I am talking about the editor functions of SSMS query editor.

Thanks,

Chris

Learning something new on every visit to SSC. Hoping to pass it on to someone else.

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 3

The () and [] brackets have a special meaning in regular expression syntax so if you need to match these characters you have to escape them by prefixing them with a \ character in the regular expression.

These two regular expressions will match the text in your example:

,substring\(\[RawData\],\(:z\),\(:z\)\),\(:z\)\)\)

,substring\(\[RawData\],\([0-9]+\),\([0-9]+\)\),\([0-9]+\)\)\)

In the second example uses [0-9]+ is an alternative way to :z of matching one or more numeric digits.

Regular expressions are powerful but strange beasts.

Stamey SSCertifiable Points: 5600 More actions · Answer 4

Thanks Andrew, that worked like a champ. The help for SSMS, BOL, does not explain enough about special characters and forming the expressions. Your example is making more sense as I look at it more, and seeing that it works is helpful for understanding it also.

Thanks,

Chris

Learning something new on every visit to SSC. Hoping to pass it on to someone else.