Substring charindex Mid - Get name within String

Question

Post reply

Substring charindex Mid - Get name within String

SQL_Kills

Hall of Fame

Points: 3154
More actions
July 28, 2022 at 3:52 pm
Go to Answer
#4069554
Hi,
I am trying to get the name inbetween the forward slash / for the column Full List
There are cases where there are no forward slashes so I will take the name that is there or there may be just one forward slash / and so I will take the name after the forward slash.
Below is the first column Full List and the Second column Name Required is the result I want.

I tried to attempt but, unable to get the second part of the substring
```
Declare @Text varchar(100)
Set @Text = 'Roger Cook / Bob Smith / Matthew Freeman / John Lowe / Barry Gibb'

Select 
LTRIM(RTRIM(SUBSTRING(@Text,CHARINDEX('/',@Text,1)+1,LEN(@Text))))
```
Thanks
- This topic was modified 2 years, 3 months ago by SQL_Kills.

Ed B

Hall of Fame

Points: 3666

July 28, 2022 at 5:34 pm

Answer

#4069601

ScottPletcher wrote:

Need the sample data in a directly usable format, not in a picture, which we can't write T-SQL against.

Show me how to make this better, if you don't mind. String split isn't recognised for some reason, which makes no sense, unless my version of ssms is too old.

DROP TABLE IF EXISTS dbo.test
CREATE TABLE test (FullList VARCHAR(1000))


INSERT dbo.test VALUES 
('Roger Cook / Bob Smith / Matthew Freeman / John Lowe / Barry Gibb'),
('Phil King / Matthew Scott / Trevor) Styke'),
('Phil King'),
('Rickey Walla / Chris Pratt')


SELECT *, 
       CASE
          WHEN b.p2 > 0
          THEN SUBSTRING(a.FullList, (b.p1 + 2), (b.p2 - b.p1 -3))
          WHEN b.p1 > 0
          THEN SUBSTRING(a.FullList, (b.p1 + 2), (LEN(a.FullList)- b.p1 -2))
          ELSE a.FullList
       END AS NameRequired
FROM dbo.test AS a
CROSS APPLY (SELECT  CHARINDEX('/',a.FullList)  AS p1,
                     CHARINDEX('/', a.FullList, (CHARINDEX('/',a.FullList)+1))   AS p2) AS b

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1358 More actions · Answer 1

Why not use STRING_SPLIT?

The spaces around the slash complicate it slightly since STRING_SPLIT accepts only a single-character delimiter, but as long as the spaces are consistent, you can handle that with REPLACE -- e.g.,

SELECT * FROM STRING_SPLIT(REPLACE(@Text,' / ','/'),'/') fullList

Just incorporate that into your query -- e.g.,

WHERE fullList.value = NameRequired

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

Need the sample data in a directly usable format, not in a picture, which we can't write T-SQL against.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101098 More actions · Answer 3

My approach was similar (I couldn't post to this site for a while):

SELECT 
    text_column,
    LTRIM(RTRIM(SUBSTRING(text_column, CASE WHEN position_after_required_name = 0 THEN 1 ELSE position_of_required_name END,
        CASE WHEN position_after_required_name = 0 THEN LEN(text_column) ELSE position_after_required_name - position_of_required_name END))) AS name_required,
    *
FROM (
    SELECT 'Roger Cook / Bob Smith / Matthew Freeman / John Lowe / Barry Gibb' UNION ALL
    SELECT 'John Smith'
) AS table_data(text_column)
CROSS APPLY (
    SELECT CHARINDEX('/', text_column + '/') + 1 AS position_of_required_name
) AS ca1
CROSS APPLY (
    SELECT CHARINDEX('/', text_column + '/', position_of_required_name) AS position_after_required_name
) AS ca2

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

Show me how to make this better, if you don't mind. String split isn't recognised for some reason, which makes no sense, unless my version of ssms is too old.

Whether STRING_SPLIT() works for you is not dependent on your SSMS version. Instead, it depends on the version of SQL Server engine you are running against and the Compatibility level of the database you are running it in.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Ed B Hall of Fame Points: 3666 More actions · Answer 5

Phil Parkin wrote:

Whether STRING_SPLIT() works for you is not dependent on your SSMS version. Instead, it depends on the version of SQL Server engine you are running against and the Compatibility level of the database you are running it in.

I think I once had to install SSMS 2012 to get a query to run using SQL 2012 syntax, because SSMS 2008 would not validate it. However, the reason I couldn't get string_split to work was because I hadn't twigged that it's a table function not a scalar function.

This appears to work, but I can't think of any way to guarantee the order for the row_num. Does the enable_ordinal flag solve this problem in SQL 2022?

SELECT x.FullList, x.Name1
FROM (
       SELECT a.FullList, b.Name1, ROW_NUMBER() OVER (PARTITION BY a.FullList ORDER BY (SELECT NULL))  RowNum      
       FROM dbo.test AS a
       CROSS APPLY (SELECT TRIM(Value) AS Name1 FROM STRING_SPLIT(a.FullList, '/')) AS b
) AS x
WHERE x.RowNum = 2
OR x.Name1 = x.FullList

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

SQL_Kills wrote:

Hi,
I am trying to get the name inbetween the forward slash / for the column Full List
There are cases where there are no forward slashes so I will take the name that is there or there may be just one forward slash / and so I will take the name after the forward slash.
Below is the first column Full List and the Second column Name Required is the result I want.
I tried to attempt but, unable to get the second part of the substring
Declare @Text varchar(100)
Set @Text = 'Roger Cook / Bob Smith / Matthew Freeman / John Lowe / Barry Gibb'

Select 
LTRIM(RTRIM(SUBSTRING(@Text,CHARINDEX('/',@Text,1)+1,LEN(@Text))))
Thanks

The DelimitedSplit8K function would make simple work of this. Post your example data as "Readily Consumable" data and one of us will show you how. See the first link under my signature line below for one of many methods to post "Readily Consumable" data and why it helps you to do so.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)