Is it possible to return only the upper case of a given string. For a string say, 'My name Is John Cramps' I should get the output as 'MIJC' I am using SQL Server 2005.

Yes, that is possible when you parse the string and only return those characters within a certain ASCII range. But why? And why in SQL Server and not in a client language?

Thank you Frank. Can you please help me with the code.

starwarsbigbang:

Thank you Frank. Can you please help me with the code.

Here's one way:

DECLARE @s varchar(100)
SELECT @s = 'My name Is John Cramps'

SELECT
    x.OnlyInitials
FROM
    (SELECT
        SUBSTRING(@s, N.Number, 1)
    FROM
        dbo.Number N
    WHERE
        N.Number BETWEEN 0 AND LEN(@s) AND
        ASCII(SUBSTRING(@s, N.Number, 1)) BETWEEN 65 AND 90
        FOR XML PATH('')) AS x(OnlyInitials)

OnlyInitials
------------
MIJC

(1 row(s) affected)

Or a more classic string-based solution:

DECLARE @S VARCHAR(100)
SET @S = 'My name Is John Cramps'

DECLARE @T VARCHAR(100), @X VARCHAR(1), @I INT, @J INT

SET @I = LEN(@S)
SET @J = 1
SET @T = ''

WHILE @J <= @I
BEGIN
 SET @X = SUBSTRING(@S, @J, 1)
 IF @X <> ' ' AND ASCII(@X) = ASCII(UPPER(@X))
 BEGIN
  SELECT @T = @T + @X
 END
 SET @J = @J + 1
END

SELECT @T

This is another appraoch. This will work well even if the string has upper case in the middle of a string

DECLARE @S VARCHAR(100)

SET @S = 'My name Is John CraMps'

DECLARE @T VARCHAR(100), @X VARCHAR(1)

SET @T = ''

SET @X = SUBSTRING(@S, 1, 1)

SELECT @T