Remove chars from string - current solutions seen are not good enough 3

[spangeman]

Simple really, I want to remove the characters from a string. E.g. 10001blah354 to 10001

I have seen a few posts regarding this subject but they will not suit my problem. As they remove all chars and leave all numbers. The above example would therefore give:-

10001blah354 to 10001354 not what I need.

The number and string I want to remove could be of any length so using left(10001blah354,5) will also not work.

So I need to remove all everything from the string after the first set of numbers have ended.

Any ideas?

Cheers
Spangeman

 

[dbomrrsm]

probably a neater way to search the alphabet but gives you the idea and it works.

select left('10001blah354',
patindex('%[a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z]%','10001blah354')-1)

[blue]DBomrrsm[/blue]

 

[nikhilparchure]

You might want to write a udf which would be generic enough

which will do the following steps

1) Loop thru the string for the its datalength
2) Check for each character with the Isnumeric function
3) return when the Isnumeric returns false


well for your string it should be
'10001blah354'

1st iteration Isnumeric (1) ResultString = '1'
2nd iteration Isnumeric (0) ResultString = '10'
3rd iteration Isnumeric (0) ResultString = '100'
4th iteration Isnumeric (0) ResultString = '1000'
5th iteration Isnumeric (1) ResultString = '10001'
6th iteration Isnumeric (b) = false exit the while
ResultString = '10001'

Someone could come with some better solution though

 

[r937]

dbomrrsm, the neater way to search the alphabet is --

select teststring
 , case
    when patindex('%[^0-9]%',teststring) > 0
    then left(teststring
        ,patindex('%[^0-9]%',teststring)-1)
    else teststring
   end as strippeddown
from teststrings

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[donutman]

One more tweak rudy.

Select left('10001blah354'+'a',
             patindex('%[^0-9]%','10001blah354')-1)

-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

Oops I put the
+'a' in the wrong place, but you get the idea.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[r937]

sorry, no, i don't get it

what's the extra 'a' for?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[donutman]

In case it's all numbers.

[Blue]SELECT[/Blue] [Fuchsia]Left[/Fuchsia][Gray]([/Gray][red]'10001'[/red][Gray],[/Gray][Fuchsia]PatIndex[/Fuchsia][Gray]([/Gray][red]'%[^0-9]%'[/red][Gray],[/Gray][red]'10001'[/red][Gray]+[/Gray][red]'a'[/red][Gray])[/Gray][Gray]-[/Gray]1[Gray])[/Gray]

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[dbomrrsm]

Thanks Rudy

Also came up with another way - which I tried earlier but it wouldnt work - guess that I must have had it slightly wrong before.

select left('100012222blah354',
patindex('%[a-z]%','100012222blah354')-1)

[blue]DBomrrsm[/blue]

 

[dbomrrsm]

sorry Donutman - didnt see your posts about adding the a - makes sense - good point.

[blue]DBomrrsm[/blue]

 

[r937]

oh, i get it, your 'a' is in case it's all numbers

well, that's why i wrote mine with a CASE, dude

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[donutman]

That's were I got the idea from.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[spangeman]

Thanks guys, didn't know about patindex will read up on it now.

Cheers
Spangeman

 

[spangeman]

This works fine

SELECT FILENAMES
 , CASE
    WHEN PATINDEX('%[^0-9]%',FILENAMES+'A') > 0
    THEN LEFT(FILENAMES
        ,PATINDEX('%[^0-9]%',FILENAMES+'A')-1)
    ELSE FILENAMES
   END AS STRIPPEDDOWN
FROM MyFiles

To make it more fun what if someone puts garbage at the front of the filename?

E.g. myfile10001.jpg

 

[r937]

spangeman, if you're gonna use my CASE construction, you would not append the 'a'

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[dbomrrsm]

great !!

Offer up the first working solution and those after get a star ?

Even when one of those is the same !!!!

[blue]DBomrrsm[/blue]

 

[donutman]

Told you life isn't fair sometimes!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]