CODEWITHSUNDEEP
sql-server-2008t-sql
AleksP
AleksP

Reputation: 1346

Find last occurence of a character within a string

How to get a string from this string if it placed between slash , need to add functionality in this function

SELECT FieldA, COALESCE(RIGHT(FieldA, NULLIF(CHARINDEX('/', REVERSE(FieldA)) - 1, -1)), FieldA, '') AS FieldB
FROM (
  SELECT '/index.cfm/fuseaction/content.page/nodeID/18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD/' AS FieldA UNION ALL
  SELECT '/index.cfm/fuseaction/content.page/nodeID/18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD' UNION ALL   
  SELECT NULL
) TableA

means for this string '/index.cfm/fuseaction/content.page/nodeID/18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD/'

or

this string '/index.cfm/fuseaction/content.page/nodeID/18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD'

or

this string '/index.cfm/fuseaction/content.page/nodeID/asdasd/'

UPD(Sorry): Not always this is GUID, and not always this is exactly content.page/nodeID - this is always some string between or start slash

Upvotes: 0

Views: 96

Answers (2)

Stephan
Stephan

Reputation: 6018

This will find ANY GUID's in your data because it uses recursion to check each string in between two forward slashes

SELECT * INTO TableA
FROM
(
        SELECT 1 AS ID,'/index.cfm/fuseaction/content.page/nodeID/18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD/' AS FieldA 
        UNION ALL
        SELECT 2,'/index.cfm/fuseaction/content.page/nodeID/18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD' 
        --UNION ALL   
        --SELECT NULL
) tableA;

--CleanTableA makes sure that all values in FieldA end with a forward slash
WITH CleanTableA
AS
(
    SELECT  ID,
            CASE
                WHEN RIGHT(FieldA,1) = '/' THEN FieldA
                ELSE FieldA + '/'
            END FieldA
    FROM tableA
),
--Recursively finds the location of each forward slash
    --RecursionCount tells me if its the first,second,third,etc... forward slash
yourRecursiveCTE
AS
(
    SELECT ID,FieldA,CHARINDEX('/',FieldA,0) AS start, 1 AS recursionCount
    FROM CleanTableA
    UNION ALL
    SELECT CleanTableA.ID,CleanTableA.FieldA,CHARINDEX('/',CleanTableA.FieldA,CHARINDEX('/',CleanTableA.FieldA,yourRecursiveCTE.start) + 1) start,recursionCount + 1
    FROM CleanTableA
    INNER JOIN yourRecursiveCTE
    ON yourRecursiveCTE.ID = CleanTableA.ID
    WHERE CHARINDEX('/',CleanTableA.FieldA,CHARINDEX('/',CleanTableA.FieldA, yourRecursiveCTE.start) + 1) <> 0
)

SELECT  A.ID,
        A.FieldA,
        --Finds the gaps in between the "/"
        SUBSTRING(A.fieldA,A.start + 1,B.start-A.start - 1) possible_guid,
        --Tries to convert them to see if they are a GUID, if so then it returns the value
        TRY_CONVERT(uniqueidentifier,SUBSTRING(A.fieldA,A.start + 1,B.start-A.start - 1)) actual_guid
FROM yourRecursiveCTE A
INNER JOIN yourRecursiveCTE B
--Only compare the same values with same ID
ON A.ID = B.ID
--Join to forwards slash before it
AND A.recursionCount = B.recursionCount -1
--Uncomment where clause to only display GUID's
--WHERE TRY_CONVERT(uniqueidentifier,SUBSTRING(A.fieldA,A.start + 1,B.start-A.start - 1)) IS NOT NULL
ORDER BY A.ID,B.recursionCount

--Cleanup
DROP TABLE TableA

Results:

ID          FieldA                                                                           possible_guid                                                                    actual_guid
----------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------------
1           /index.cfm/fuseaction/content.page/nodeID/18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD/  index.cfm                                                                        NULL
1           /index.cfm/fuseaction/content.page/nodeID/18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD/  fuseaction                                                                       NULL
1           /index.cfm/fuseaction/content.page/nodeID/18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD/  content.page                                                                     NULL
1           /index.cfm/fuseaction/content.page/nodeID/18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD/  nodeID                                                                           NULL
1           /index.cfm/fuseaction/content.page/nodeID/18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD/  18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD                                             18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD
2           /index.cfm/fuseaction/content.page/nodeID/18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD/  index.cfm                                                                        NULL
2           /index.cfm/fuseaction/content.page/nodeID/18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD/  fuseaction                                                                       NULL
2           /index.cfm/fuseaction/content.page/nodeID/18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD/  content.page                                                                     NULL
2           /index.cfm/fuseaction/content.page/nodeID/18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD/  nodeID                                                                           NULL
2           /index.cfm/fuseaction/content.page/nodeID/18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD/  18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD                                             18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD

Upvotes: 2

Morphed
Morphed

Reputation: 3619

For those people who love monster 1-liners.

For MS2012 - Using IIF:

SELECT IIF(CHARINDEX('/', REVERSE(FieldA)) = 1,REVERSE(SUBSTRING(REVERSE(FieldA),2,CHARINDEX('/', SUBSTRING(REVERSE(FieldA), 2, LEN(FieldA))) - 1)),REVERSE(SUBSTRING(REVERSE(FieldA),1,CHARINDEX('/', REVERSE(FieldA)) - 1)))
FROM (
  SELECT '/index.cfm/fuseaction/content.page/nodeID/18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD/' AS FieldA 
  UNION ALL
  SELECT '/index.cfm/fuseaction/content.page/nodeID/18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD' 
  UNION ALL   
  SELECT NULL
) TableA

For MS2008 Here is the same code in a CASE

SELECT A.FieldA
    ,   CASE 
            WHEN CHARINDEX('/', REVERSE(FieldA)) = 1 THEN REVERSE(SUBSTRING(REVERSE(FieldA),2,CHARINDEX('/', SUBSTRING(REVERSE(FieldA), 2, LEN(FieldA))) - 1)) 
            ELSE REVERSE(SUBSTRING(REVERSE(FieldA),1,CHARINDEX('/', REVERSE(FieldA)) - 1))
END
FROM (
  SELECT '/index.cfm/fuseaction/content.page/nodeID/18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD/' AS FieldA 
  UNION ALL
  SELECT '/index.cfm/fuseaction/content.page/nodeID/18156F6E-88E3-42FC-BDCB-DA6CBAD14EFD' 
) a

Upvotes: 3

Related Questions