Convert MSSQL TSQL to a Formula

GWilliy · #1 (**permalink**) 02-23-09, 05:13

Hi Folks

First time I've posted in the Excel section, I normally float around in the SQL Server Section.

I have a TSQL Statement that returns an Insurance Policy Number in a Business Format from a Database SQL Format and wonder if anyone could tell me how I could do the same thing in an Excel Formula.

The System Policys are represented as a String made up of three sections

1-5 numbers (1 to 19,999)
A Character a-z (A to Z)
1-2 numbers (0 to 99)

in that Order

I would like to flip the cell value with a formula into

1-2 numbers (0 to 99)
A Character a-z (A to Z)
1-5 numbers (1 to 19,999)

The TSQL I use to do this is:-

Code:

 
SET @PatInd = PATINDEX('%[a-z]%',@InPolicy)
SELECT SUBSTRING(@InPolicy,@PatInd +1,LEN(@InPolicy)- @PatInd)
+
SUBSTRING(@InPolicy,@PatInd,1)
+
LEFT(@InPolicy,@PatInd -1)

where PatIndex is TSQL for returning the integer position of a charcter within a String

examples Policys are

Code:

 
System Business
501A80 80A501
1000G80 80G1000
10001K80 80K10001
10000K80 80K10000

Any help muchly appreciated

GW

GWilliy · #2 (**permalink**) 02-24-09, 08:28

Mmmmm 25 Views and no takers?

Is this a difficult one ?

pootle flump · #3 (**permalink**) 02-24-09, 10:55

No equivalent to PATINDEX in Excel. You'll probably have to create a UDF in VBA and loop through the characters, testing (for example) the ASCII value until you find the first alpha character.

Internet Services

Web Development

Digital Marketing

Consumer Tech