[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

String parsing

(Kathryn)

3/27/2007 7:25:00 PM

Good evening, all!

I have a field that will contain between 2 and four alpha characters
and upto 8 numbers. So all of these are valid:

AB12335
MEAB4567
RNAP3456789
XY87654321

I need to left pad the number to be exactly eight digits. So I need
the above to be:

AB00012335
MEAB00004567
RNAP03456789
XY87654321

The only way I can see to do this is to loop over the the field,
parsing characters one by one, starting at the right most character.
If I get to an alpha character before I get to the ninth character,
insert the appropriate number of zeros.

I keep thinking that there must be a quicker way to do it than this.
Is there, or is that jus wishful thinking?

Kathryn
2 Answers

Aaron [SQL Server MVP]

3/27/2007 7:33:00 PM

0

Assuming all values have at least one numeric digit...




CREATE TABLE #foo(s VARCHAR(16))

SET NOCOUNT ON;

INSERT #foo SELECT 'AB12335'
UNION ALL SELECT 'MEAB4567'
UNION ALL SELECT 'RNAP3456789'
UNION ALL SELECT 'XY87654321';

SELECT
s,
s_adjusted = LEFT(s, i-1) + RIGHT('00000000'+SUBSTRING(s, i, LEN(s)), 8)
FROM
(
SELECT
s,
i = PATINDEX('%[0-9]%', s)
FROM
#foo
) x;

DROP TABLE #foo;

--
Aaron Bertrand
SQL Server MVP
http://www.sq...
http://www.aspfa...




"kbutterly" <kbutterly@yahoo.com> wrote in message
news:1175023483.789081.14030@n59g2000hsh.googlegroups.com...
> Good evening, all!
>
> I have a field that will contain between 2 and four alpha characters
> and upto 8 numbers. So all of these are valid:
>
> AB12335
> MEAB4567
> RNAP3456789
> XY87654321
>
> I need to left pad the number to be exactly eight digits. So I need
> the above to be:
>
> AB00012335
> MEAB00004567
> RNAP03456789
> XY87654321
>
> The only way I can see to do this is to loop over the the field,
> parsing characters one by one, starting at the right most character.
> If I get to an alpha character before I get to the ninth character,
> insert the appropriate number of zeros.
>
> I keep thinking that there must be a quicker way to do it than this.
> Is there, or is that jus wishful thinking?
>
> Kathryn
>


(Kathryn)

3/28/2007 12:01:00 PM

0

Aaron,

Thanks so much! Works like a charm.

Kathryn

On Mar 27, 3:33 pm, "Aaron Bertrand [SQL Server MVP]"
<ten....@dnartreb.noraa> wrote:
> Assuming all values have at least one numeric digit...
>
> CREATE TABLE #foo(s VARCHAR(16))
>
> SET NOCOUNT ON;
>
> INSERT #foo SELECT 'AB12335'
> UNION ALL SELECT 'MEAB4567'
> UNION ALL SELECT 'RNAP3456789'
> UNION ALL SELECT 'XY87654321';
>
> SELECT
> s,
> s_adjusted = LEFT(s, i-1) + RIGHT('00000000'+SUBSTRING(s, i, LEN(s)), 8)
> FROM
> (
> SELECT
> s,
> i = PATINDEX('%[0-9]%', s)
> FROM
> #foo
> ) x;
>
> DROP TABLE #foo;
>
> --
> Aaron Bertrand
> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfa...
>