What is the best way to convert this to SQL

Question

I know for the Database Guru's here this should be a doddle. I have a Field in my database in the format of ' A/B/C/D/E/F '

The format is irrelevant I generally need the last two parts so for the above it would be

'EF'

But if I had another string

AB/CD/EF/GH == EFGH

And I am looking to getting the last two parts to return like this 'EFGH'

Does anyone know an SQL Function I can do that will split this

I am using Microsoft SQL Server 2012 - I Hope this helps,

Here is C# Code.

var myText = "A/B/C/D/E/F";
var identificationArray = myText.Split('/');

if(identificationArray.Length >= 2)
{
    var friendlyId = identificationArray[identificationArray.Length - 2] + identificationArray[identificationArray.Length - 1];

    return friendlyId;
}
return "";

This can be very specific to the platform (DBMS) you are using. Could you update your question with the platform? The database, .net and optimization tags seem superfluous in this case. — crthompson, Jul 25 '14 at 16:19
Is your format `A/B/C/D/E/F` or `AB/CD/EF/GH`? You have an example both ways. — TyCobb, Jul 25 '14 at 16:26
I do not intend to keep this question up forever I will delete when I have an answer as i Understand this is probably a waste of SO — TheMonkeyMan, Jul 25 '14 at 16:31
This is not a waste, it is a perfectly valid question. I'm not sure what the answer is, but I did find [this post](http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x) that you can reference. — AdamMc331, Jul 25 '14 at 16:32
Its something that Most Developers should know - So its a question I feel ashamed to look up and learn the SQL Behind it. — TheMonkeyMan, Jul 25 '14 at 16:35
So you're saying that it's less shameful to ask something you should know than to look up something you should know? — Dan Bracuk, Jul 25 '14 at 16:40
Asking a question (no matter how simply you may think it is) and then deleting it after you get an answer goes against the spirit of Stack Overflow. I wouldn't answer a question if I knew the poster was going to delete it once they got what they were looking for. — Tim, Jul 25 '14 at 16:41
Nope - I am saying its more shameful that I dont know it but at least I am not an idiot and spend time trying to figure out something that Is a relatively simple task for someones help I would rather delete as I feel that SO is like a community and to keep the boards high quality i feel that deleting keeps the crap out — TheMonkeyMan, Jul 25 '14 at 16:41
Don't be ashamed. This is the place to ask questions. Take a look at the question I referenced, and when you are able to solve the problem, feel free to answer your own question and share your knowledge with everyone else. Someday, someone will have the same problem and may be lead to your question here for the solution. — AdamMc331, Jul 25 '14 at 16:42
@TheMonkeyMan, you should also be aware that if you delete too many posts, you'll be banned from posting questions at all. Questions and answers are what we do here. You're in the right place. — crthompson, Jul 25 '14 at 17:04
@TheMonkeyMan, also, remember, the points that people receive for answering your question go away if you delete the question. (actually, i'm not sure you can delete it w/ upvoted answers) but it does a disservice to those that are helping you. — crthompson, Jul 25 '14 at 17:06

Paul Williams · Answer 1 · 2014-07-25T17:59:36.193

Here is one answer that searches a string in reverse order for the second forward slash and returns that substring with forward slashes removed:

declare @s varchar(20)
set @s = 'A/B/C/D/E/F'

-- result: 'EF'
select reverse(replace(left(reverse(@s), charindex('/', reverse(@s), charindex('/', reverse(@s)) + 1)), '/', ''))

set @s = 'AB/CD/EF/GH'

-- result: 'EFGH'
select reverse(replace(left(reverse(@s), charindex('/', reverse(@s), charindex('/', reverse(@s)) + 1)), '/', ''))

Testing this with a couple of other inputs:

set @s = '/AB/CD' -- result: 'ABCD'
set @s = 'AB/CD'  -- result: an empty string '' -- you may not want this result
set @s = 'AB'     -- result: an empty string ''

Here is a ridiculously complicated way to do the same thing with a series of common table expressions (CTEs). Credit goes to Itzik Ben-Gan for the CTE technique to generate a tally table using cross-joins:

declare @s varchar(50)
set @s = 'A/B/C/D/E/F/G'
--set @s = 'AB/CD/EF/GH'
--set @s = 'AB/CD'
--set @s = 'ABCD/EFGH/IJKL'
--set @s = 'A/B'
-- set @s = 'A'

declare @result varchar(50)
set @result = ''

;with
-- cross-join a meaningless set of data together to create a lot of rows
Nbrs_2    (n) AS (SELECT 1 UNION SELECT 0 ),
Nbrs_4    (n) AS (SELECT 1 FROM Nbrs_2     n1 CROSS JOIN Nbrs_2     n2),
Nbrs_16   (n) AS (SELECT 1 FROM Nbrs_4     n1 CROSS JOIN Nbrs_4     n2),
Nbrs_256  (n) AS (SELECT 1 FROM Nbrs_16    n1 CROSS JOIN Nbrs_16    n2),
Nbrs_65536(n) AS (SELECT 1 FROM Nbrs_256   n1 CROSS JOIN Nbrs_256   n2),
Nbrs      (n) AS (SELECT 1 FROM Nbrs_65536 n1 CROSS JOIN Nbrs_65536 n2),
-- build a table of numbers from the data above; this is insanely fast
nums(n) as
(
   select row_number() over(order by n) from Nbrs
),
-- split the string into separate rows per letter
letters(n, c) as
(
    select n, substring(@s, n, 1)
    from nums
    where n < len(@s) + 1
),
-- count the slashes from the rows in descending order
-- the important slash is the second one from the end
slashes(n, num) as
(
    select n, ROW_NUMBER() over (order by n desc)
    from letters
    where c = '/'
)
select @result = @result + c
from letters
where n > (select n from slashes where num = 2) -- get everything after the second slash
and c <> '/' -- and drop out the other slash

select @result

Nice, I like how you managed to do it without a case statement — Aducci, Jul 25 '14 at 17:08
@Aducci Thank you. I'm not sure all cases match exactly what the OP wants, but it's close. We came up with very similar solutions! — Paul Williams, Jul 25 '14 at 17:57

score 3 · Answer 2 · answered Jul 25 '14 at 17:00

You need to reverse the string and find the 2nd occurrence of the / character. Once you have that it is pretty straight forward, just a lot of function calls to get the desired format

declare @test varchar(max);
set @test = 'b/b/a/v/d';

select 
    case
        when charindex('/', reverse(@test), charindex('/', reverse(@test))+1) = 0 then ''
        else replace(reverse(substring(reverse(@test), 0, charindex('/', reverse(@test), charindex('/', reverse(@test))+1))), '/', '')
    end

score 1 · Answer 3 · answered Jul 25 '14 at 18:10

1

I understand that you want to do this in SQL. But did you think about using SQL CLR User Defined Functions? It will execute faster than SQL. you anyways have the logic implemented in C# which definitely simpler than the logic in SQL.

answered Jul 25 '14 at 18:10

Vaibhav Pingle

575
1
9
27

David Brabant · Answer 4 · 2014-07-25T17:29:54.317

Late to the party, but here is my attempt:

declare @text varchar(max), @reversedtext varchar(max)

select @text = 'AB/CD/EF/GH'
select @reversedtext = reverse(@text)

declare @pos1 int
declare @pos2 int
declare @pos3 int


select @pos1 = charindex('/', @reversedtext)
select @pos2 = charindex('/', replace(@reversedtext, left(@reversedtext, @pos1), ''))
select @pos3 = @pos1 + @pos2

select REPLACE(RIGHT(@text, @pos3), '/', '')

What is the best way to convert this to SQL

4 Answers4