SQL Server substring w/o custom functions

Question

I have a dilemma regarding how to extract a sub string from a larger string without using custom functions The string is of this form:

"    [1].[2]"
"        [1].[2].[3]"
"            [1].[2].[3].[4]"

Basically, it's a hierarchy that has 4 leading spaces for every child of a node. The task is to maintain those 4 leading spaces for every node, but get only the child in the end, without the full path

So, the final result should be something like this:

"    [2]"
"        [3]"
"            [4]"

Could anyone help me?

Edit: or, if it's the case, should I modify the CTE that is building the result set?

The CTE is like the one below:

WITH Hierarchy(cid, cname, parent_id, Parents, nname)
AS
(
    SELECT map_id, name, parent_id, CAST('' AS VARCHAR(MAX)), CAST(''+name AS VARCHAR(MAX))
        FROM tblMapping AS tm1
        WHERE parent_id = 0    
    UNION ALL
    SELECT tm.map_id, tm.name, Parent.cid,
    CAST(CASE WHEN Parent.Parents = ''
        THEN(CAST(tm.parent_id AS VARCHAR(MAX)))
        ELSE(Parent.Parents + '.' + CAST(tm.parent_id AS VARCHAR(MAX)))
    END AS VARCHAR(MAX)),
    -- add the formated column description
    CAST(CASE WHEN Parent.Parents = ''
        THEN(Parent.cname)
        ELSE('    '+Parent.nname+ '.'+tm.name)
    END AS VARCHAR(MAX))
        FROM tblMapping AS tm
        INNER JOIN Hierarchy AS Parent ON tm.parent_id = Parent.cid    
)
SELECT *
    FROM Hierarchy
ORDER BY ltrim(nname)
OPTION(MAXRECURSION 32767)

Thank you!

I have the CTE in place, it's showing up the final result as shown in the request but, I don't know how to count the spaces before the text and add them to another column.. — rosuandreimihai
– rosuandreimihai, Commented Jun 8, 2017 at 10:20

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Try something like this

UPDATED: Inlcudes a root node now...

DECLARE @mockup TABLE(YourString VARCHAR(100));
INSERT INTO @mockup VALUES
 (' [1]')
,('    [1].[2]')
,('        [1].[2].[3]')
,('            [1].[2].[3].[4]');

SELECT LEFT(YourString,CHARINDEX('[',YourString)-1)
      +SUBSTRING(YourString,LEN(YourString)+1-CHARINDEX('[',REVERSE(YourString)),1000)
FROM @mockup;

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 8, 2017 at 10:27

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

rosuandreimihai Over a year ago

Ok, it seems to work, but, what if I have a parent node without any trailing spaces, "[1]" that is on top of all of the records? It will not be displayed at all.. Anyway, it works for almost all of them!

Gottfried Lesigang Over a year ago

@rosuandreimihai No problem, search for the [ instead of the . and use +1 instead of +1. This should work...

rosuandreimihai Over a year ago

Perfect! Thank you!

Gottfried Lesigang Over a year ago

@rosuandreimihai Glad to read this, updated my answer for completeness...

Yossi Vainshtein · Accepted Answer · 2017-06-08 10:28:30Z

0

I would concatenate the spaces part with the last index. Something like

Select left(your_string, char_index(your_string, "[") - 1) + 
       right(your_string, 3) from your_table

The 3 is assuming there's always one digit. If not, this is a bit complex because no last index of in SQLServer:

 Select left(your_string, char_index(your_string, "[") - 1) + 
        reverse(left(reverse(your_string), char_index(reverse(your_string), "[")))
        from your_table

edited Jun 8, 2017 at 10:28

answered Jun 8, 2017 at 10:18

Yossi Vainshtein

4,0455 gold badges26 silver badges44 bronze badges

1 Comment

rosuandreimihai Over a year ago

What happens if the number inside the brackets is larger the 9?

Collectives™ on Stack Overflow

SQL Server substring w/o custom functions

2 Answers 2

UPDATED: Inlcudes a root node now...

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

UPDATED: Inlcudes a root node now...

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related