Select only one character type in string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Select only one character type in string

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2012-05-23 : 05:22:24

Hi,

Curious requirement this, but I couldn't see a straightforward way of doing it using the standard string handling functions.

I want to be able to select all instances of one character from a string. So say, the target was 'T' and the string was:

'This is a string, not an int'

The result would be:

'Tttt'

Or if the target was 'I' then the result would be:

'iiii'

Is there a way to do this?

Cheers,
Matt

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2012-05-23 : 06:20:58

Try something like this:


SELECT S.new_str
  FROM (VALUES('This is a string, not an int')) AS T(str)
       CROSS APPLY
       (SELECT SUBSTRING(T.str, V.number, 1)
          FROM master..spt_values AS V
         WHERE V.type = N'P'
           AND V.number <= LEN(T.str)
           AND SUBSTRING(T.str, V.number, 1) = 't'
           FOR XML PATH('')) AS S(new_str)

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

mattt
Posting Yak Master

194 Posts

Posted - 2012-05-23 : 08:19:18

That's great, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources