ganeshkumar005 (2/21/2012)

---

I have a requirement here. There is a string '0005 ganeshkumar A999'. I want the last four character 'A999' as output where it is not necessarily a four charactes only. It could be 3 to 6 characters also.

Thanks in advance for your any help coming here, as always.

Regards,
Ganesh.

--DECLARE @String VarChar(MAX) = '0005 ganeshkumar A999';
--DECLARE @String VarChar(MAX) = 'first second third fourth';
DECLARE @String VarChar(MAX) = 'first second';

DECLARE @EndString VarChar(MAX);

SET @EndString = LTRIM(REVERSE(LEFT(REVERSE(@String),PATINDEX('% %',@String))));

SELECT @EndString

select parsename(replace('0005 ganeshkumar A999',' ','.'),1)

--first some sample data

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable

CREATE TABLE #TempTable (
    [ID] INT IDENTITY(1,1) NOT NULL,
    [Address] NVARCHAR(50) NULL,
    PRIMARY KEY (ID))

INSERT INTO #TempTable
SELECT '1 north main st'
UNION
SELECT '12 main steet'

SELECT
     ID
    ,[1]+[2] AS Street
FROM
    (
    SELECT 
         tt.ID
        ,dsk.ItemNumber
        ,dsk.Item 
    FROM 
        #TempTable AS tt
    CROSS APPLY
        dbo.DelimitedSplit8K(tt.Address,' ') AS dsk
    WHERE
        ItemNumber IN (1,2)
    ) AS src
PIVOT (MAX(Item) FOR ItemNumber IN ([1],[2])) AS pvt

WITH MyData ([address]) AS (
    SELECT '1 north main st'
    UNION ALL SELECT '12 main street'
    UNION ALL SELECT '14         clover          avenue')
SELECT [address], [address1], [address2]=LEFT([address2], CHARINDEX(' ', [address2])-1)
FROM MyData
-- Remove extra white space
CROSS APPLY (
    SELECT  REPLACE(
                REPLACE(
                    REPLACE(address, '  ', ' ' + CHAR(7))
                ,CHAR(7) + ' ', CHAR(7))
            ,CHAR(7), '')) a([address1])
CROSS APPLY (
    SELECT STUFF([address1], CHARINDEX(' ', [address1]), 1, '')) b([address2])