Help me with a query

[aNubies]

Guys, I just want to get some specific characters in this figures using query. Lets assume that the figures like this:

ID-XXXXX-PPPPP
XXXXX-ID-PPPPP
XXXXX-PPPPP-ID
PPP-ID-XXXXXX

Now what i want to get is all the 'P's only no matter what position it is. So the query should be return this set of characters:

PPPPP
PPPPP
PPPPP
PPP

Please help me out here. Thanks

[TysonLPrice]

If the values are alway 'ID', '-', and 'X' you could use:

select replace(replace(replace('ID-XXXXX-PPPPP','id',''),'x',''),'-','')

Otherwise a different solution is needed.

[techgnome]

I suspect that the X's at least are place holders and represent numbers.... so it's more likely to be ID-1234-PPPP .... and even the PPPP could be place holders for something else... making this a potential exercise in futility. A format patter (which you have presented) will only allow us to go so far... Data pattern is probably much more telling at this point.

-tg

[aNubies]

@Techgnome - You are absolutely right those are just a format patterns which will be replace by an actual data. SO basically the reason I need to get the P's only for filtering purposes. I just made a code that works perfectly for it but on the other hand it runs for 29-30ms for execution so im looking for other alternatives that could process a little faster as mine.

[TysonLPrice]

Just playing around :-)

CREATE FUNCTION VBForums
(@Input VarChar(50), @WhatToKeep Char(1)) 
RETURNS Varchar(50)
AS

begin
declare @WorkArea Varchar(50)
declare @ctr int
Declare @Index int

set @ctr = Len(@Input)
set @Index = 1
set @WorkArea = ''

while @ctr > 0

	begin

	if substring(@Input,@index,1) = @WhatToKeep set @WorkArea = @WorkArea + substring(@Input,@index,1)
	set @Index = @Index + 1
	set @Ctr = @ctr -1

	end

return @workarea

end

Pass it the string and what character you want to keep.

select dbo.VBForums('ID-XXXXX-PPPPP','p')

Returns PPPPP