pull # from string 2

[slybitz]

So I have a column in a table with varchar values. The values can look like "#12345 abcd" or "abcd #1234" (without the quotes). I want to pull out just the number from those strings of characters. So in other words if the field value is "#1234 abcd" i want to return just "1234". How can I do this within a select statement? Thanks.

 

[slybitz]

I forgot to mention that the actual number can vary in length and so can the entire string of characters.

 

[monksnake]

Will the numbers you want pulled be the only numbers in the string??

<.

 

[slybitz]

Yes.

 

[LNBruno]

Don't think you can do it with a simple SELECT since it sounds like you'll need to check each character in the string....

Maybe a function using PATINDEX?

< M!ke >
I am not a hamster and life is not a wheel.

 

[bborissov]

[COLOR=blue]DECLARE[/color] @Test [COLOR=blue]table[/color] (YourField [COLOR=blue]varchar[/color](200))
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'#12345 abcd'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'abcd #12345'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'abcd #12345 asdasdas'[/color])


[COLOR=blue]SELECT[/color] [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] [COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'#'[/color], YourField) = 0
                 [COLOR=blue]THEN[/color] [COLOR=red]'0'[/color]
            [COLOR=blue]WHEN[/color] [COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]' '[/color],[COLOR=#FF00FF]SUBSTRING[/color](YourField,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'#'[/color], YourField)+1,8000)) = 0
                 [COLOR=blue]THEN[/color] [COLOR=#FF00FF]SUBSTRING[/color](YourField,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'#'[/color], YourField)+1,8000)
            [COLOR=blue]ELSE[/color] [COLOR=#FF00FF]SUBSTRING[/color](YourField,
                           [COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'#'[/color], YourField)+1,
                           [COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]' '[/color],[COLOR=#FF00FF]SUBSTRING[/color](YourField,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'#'[/color], YourField)+1,8000))-1)
             [COLOR=blue]END[/color]
[COLOR=blue]FROM[/color] @test

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[LNBruno]

This seems to work:

DECLARE @NumericChar SMALLINT
   , @string VARCHAR(8000)
   
SET @string = '#1234abcd'
BEGIN
   SET @NumericChar = PATINDEX('%[^0-9]%', @string)

   WHILE @NumericChar > 0
      BEGIN
         SET @string = STUFF(@string, @NumericChar, 1, '')
         SET @NumericChar = PATINDEX('%[^0-9]%', @string)
      END
   PRINT @string
END

< M!ke >
I am not a hamster and life is not a wheel.

 

[LNBruno]

LOL! I said "not with a simple SELECT" Boris!

Good one!

< M!ke >
I am not a hamster and life is not a wheel.

 

[monksnake]

I wrote a small function that looks very much like one I saw.

declare @findString varchar(30)
declare @foundNumber varchar(10)
declare @length tinyint

set @foundNumber = ''
set @findString = 'wer#23452548tyfdi'
select @length = charindex('#', @findString) + 1
while @length < Len(@findString)
   begin 
      if substring(@findString, @length, 1) >= '0' and substring(@findString, @length, 1) <= '9'
         begin   
            set @foundNumber = @foundNumber + substring(@findString, @length, 1)
         end 
      else
         begin 
            set @length = Len(@findString)          
         end
      set @length = @length + 1
   end 

Select convert(int, @foundNumber)

<.

 

[bborissov]

Did you saw somewhere I said that this is a simple SELECT

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[LNBruno]

You deserve two stars, Borislav: one for your solution, and one for that excellent come back! Guess you'll just have to split it in half unless someone offers another.

:-D

< M!ke >
I am not a hamster and life is not a wheel.

 

[ESquared]

slybitz, what do you want to happen when there are two numbers in the string, such as "#12345 abcd #1234"?

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[monksnake]

I asked about that higher up E^2.

<.

 

[slybitz]

bborissov - perfect! thank you so much. brilliant.

As for the others that suggested their ideas.. i appreciate it as well.

 

[bborissov]

Not so brilliant as you thing, but should work in your case.
As ESquared points that solution wouldn't work if you have two or more numbers in the string


Mike, getting TWO stars for ONE simple SELECT is more than enough

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ESquared]

Borislav, your query can be simplified slightly, take out the second case and add the part in red:

SELECT CASE WHEN CHARINDEX('#', YourField) = 0
                 THEN '0'
            ELSE SUBSTRING(YourField,
                           CHARINDEX('#', YourField)+1,
                           CHARINDEX(' ',SUBSTRING(YourField,CHARINDEX('#', YourField)+1,8000) [red]+ ' '[/red])-1)
             END
FROM @test

There's also another option which does one less CharIndex (replacing it with an NullIf and an IsNull):

SELECT
   IsNull(SubString(
      YourField, 
      NullIf(CharIndex('#', YourField), 0) + 1, 
      CharIndex(' ', SubString(YourField, CharIndex('#', YourField) + 1, 8000) + ' ') - 1
   ), 0)
FROM @test

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[LNBruno]

To maybe close this out...

For the "if you have two or more numbers in the string" situation, the block of code I posted will actually return ALL numerics in the string, but as a single value (i.e., ""#12345 abcd #12345" will display as "1234512345").

Thanks again, Borislav, for a nice piece of code and a very needed laugh!

< M!ke >
I am not a hamster and life is not a wheel.

 

[ESquared]

Yes LNBruno that's what I was driving at.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[slybitz]

Well fortunately there is only one set of numbers in the string so it works out. But LNBruno's idea does indeed work if there are two sets of numbers. Thanks.