charindex/substring to replace varying amounts of '/'

Question

Post reply

charindex/substring to replace varying amounts of '/'

simon_s

Ten Centuries

Points: 1358
More actions
August 11, 2015 at 10:57 am

#398881

I wonder if someone can help before my head explodes
Ive got the following data in a table
sourcedata
/@836d254c/v1/HOME/INDEX
/@836d254c/v1/ASSIST/SPECIAL
/@836d254c/v1/ASSIST/SPECIAL
/@836d256c/v1/api/ASSIST/Regular/Getdata
I need to split it between two separate columns in the following format
Column1
/@836d254c
/@836d254c
/@836d254c
/@836d256c
Column2
/v1/HOME/INDEX
/v1/ASSIST/SPECIAL
/v1/ASSIST/SPECIAL
/v1/api/ASSIST/Regular/Getdata
I've tried using something like
update simons_table
set column1 = sourcedata
update [simons_table]
set [column2] = REVERSE( substring(REVERSE([column1]),1,charindex('/',reverse([column1]))))
where sourcedata like '/@%'
update [simons_table]
set [column1] = replace (column1,REVERSE( substring(REVERSE([column1]),1,charindex('/',reverse([column1])))),'')
where sourcedata like '/@%'
update [simons_table]
set [column2] = REVERSE( substring(REVERSE([column1]),1,charindex('/',reverse([column1]))))+column2
where sourcedata like '/@%'
update [simons_table]
set [column1] = replace (column1,REVERSE( substring(REVERSE([column1]),1,charindex('/',reverse([column1])))),'')
where sourcedata like '/@%'
But as each row of data in the sourcedata column can have varying amounts of '/' I'm stuck as to how to get it to work and my head might explode at this rate

Viewing 11 posts - 1 through 10 (of 10 total)

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

Quick suggestion

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @sourcedata TABLE(STR_DATA VARCHAR(100) NOT NULL);

INSERT INTO @sourcedata(STR_DATA)

VALUES

('/@836d254c/v1/HOME/INDEX' )

,('/@836d254c/v1/ASSIST/SPECIAL' )

,('/@836d256c/v1/api/ASSIST/Regular/Getdata');

SELECT

SD.STR_DATA AS ORIGINAL

,LEFT(SD.STR_DATA,CHARINDEX('/v1/',SD.STR_DATA)-1) AS COLUMN_1

,REPLACE(SD.STR_DATA,LEFT(SD.STR_DATA,CHARINDEX('/v1/',SD.STR_DATA)-1),'') AS COLUMN_2

FROM @sourcedata SD;

Results

ORIGINAL COLUMN_1 COLUMN_2

------------------------------------------ ----------- -------------------------------

/@836d254c/v1/HOME/INDEX /@836d254c /v1/HOME/INDEX

/@836d254c/v1/ASSIST/SPECIAL /@836d254c /v1/ASSIST/SPECIAL

/@836d256c/v1/api/ASSIST/Regular/Getdata /@836d256c /v1/api/ASSIST/Regular/Getdata

sgmunson SSC Guru Points: 110640 More actions · Answer 2

In order to have ANY chance at splitting this string, you HAVE to have something that is consistently true across ALL rows. Some possibilities include:

1.) The 2nd slash always starts the 2nd field.

2.) The slash immediately preceding v1 starts the 2nd field. --EDIT: Eirikur's code is an example of this, and differs only by assuming the need for an additional / after the v1

3.) The first x number of characters represents the first field.

There might be other possibilities that you will know, but I won't, but the idea is that there simply HAS to be something that is consistently true across every single row, or you are going to have a problem. If you can describe such a condition, it can probably be coded. Let us know...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

drew.allen SSC Guru Points: 76985 More actions · Answer 3

As an added note, REVERSE should generally only be used when it is easier to define your criteria reading from the right than it is reading from the left. So, if you were looking for the last "/". Your examples look like you are actually looking for the first "/", which means that you want to be reading from the left, so you don't want to use REVERSE.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

A slightly different version of Eirikur's code. This is just to give you more ideas on the different possibilities to solve your problem.

DECLARE @sourcedata TABLE(STR_DATA VARCHAR(100) NOT NULL);

INSERT INTO @sourcedata(STR_DATA)

VALUES

('/@836d254c/v1/HOME/INDEX' )

,('/@836d254c/v1/ASSIST/SPECIAL' )

,('/@836d256c/v1/api/ASSIST/Regular/Getdata');

SELECT

SD.STR_DATA AS ORIGINAL

,LEFT(SD.STR_DATA,CHARINDEX('/',SD.STR_DATA, 2)-1) AS COLUMN_1

,SUBSTRING(SD.STR_DATA,CHARINDEX('/',SD.STR_DATA, 2), 8000) AS COLUMN_2

FROM @sourcedata SD;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 5

Luis Cazares (8/11/2015)
A slightly different version of Eirikur's code. This is just to give you more ideas on the different possibilities to solve your problem.
DECLARE @sourcedata TABLE(STR_DATA VARCHAR(100) NOT NULL);
INSERT INTO @sourcedata(STR_DATA)
VALUES
('/@836d254c/v1/HOME/INDEX' )
,('/@836d254c/v1/ASSIST/SPECIAL' )
,('/@836d254c/v1/ASSIST/SPECIAL' )
,('/@836d256c/v1/api/ASSIST/Regular/Getdata');
SELECT
SD.STR_DATA AS ORIGINAL
,LEFT(SD.STR_DATA,CHARINDEX('/',SD.STR_DATA, 2)-1) AS COLUMN_1
,SUBSTRING(SD.STR_DATA,CHARINDEX('/',SD.STR_DATA, 2), 8000) AS COLUMN_2
FROM @sourcedata SD;

Nice!

--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)

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 6

For giggles here is yet another slightly modified version. The difference doesn't look much but it is an improvement over the first one of roughly 20% and 5-10% over Luis's version.

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @sourcedata TABLE(STR_DATA VARCHAR(100) NOT NULL);

INSERT INTO @sourcedata(STR_DATA)

VALUES

('/@836d254c/v1/HOME/INDEX' )

,('/@836d254c/v1/ASSIST/SPECIAL' )

,('/@836d256c/v1/api/ASSIST/Regular/Getdata');

;WITH BASE_DATA AS

(

SELECT

SD.STR_DATA AS ORIGINAL

,CHARINDEX('/',SD.STR_DATA,2) AS POS

FROM @sourcedata SD

)

SELECT

BD.ORIGINAL

,LEFT(BD.ORIGINAL,BD.POS - 1)

,SUBSTRING(BD.ORIGINAL,BD.POS,8000)

FROM BASE_DATA BD;

simon_s Ten Centuries Points: 1358 More actions · Answer 7

many thanks for all the help , appreciate that 🙂

WayneS SSC Guru Points: 95460 More actions · Answer 8

Eirikur Eiriksson (8/12/2015)
For giggles here is yet another slightly modified version. The difference doesn't look much but it is an improvement over the first one of roughly 20% and 5-10% over Luis's version.
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @sourcedata TABLE(STR_DATA VARCHAR(100) NOT NULL);
INSERT INTO @sourcedata(STR_DATA)
VALUES
('/@836d254c/v1/HOME/INDEX' )
,('/@836d254c/v1/ASSIST/SPECIAL' )
,('/@836d254c/v1/ASSIST/SPECIAL' )
,('/@836d256c/v1/api/ASSIST/Regular/Getdata');
;WITH BASE_DATA AS
(
SELECT
SD.STR_DATA AS ORIGINAL
,CHARINDEX('/',SD.STR_DATA,2) AS POS
FROM @sourcedata SD
)
SELECT
BD.ORIGINAL
,LEFT(BD.ORIGINAL,BD.POS - 1)
,SUBSTRING(BD.ORIGINAL,BD.POS,8000)
FROM BASE_DATA BD;

Eirikur,

Since you're doing a performance test, how does this compare?

DECLARE @sourcedata TABLE(STR_DATA VARCHAR(100) NOT NULL);

INSERT INTO @sourcedata(STR_DATA)

VALUES

('/@836d254c/v1/HOME/INDEX' )

,('/@836d254c/v1/ASSIST/SPECIAL' )

,('/@836d256c/v1/api/ASSIST/Regular/Getdata');

SELECT

SD.STR_DATA AS ORIGINAL

,LEFT(SD.STR_DATA,ca.pos-1) AS COLUMN_1

,SUBSTRING(SD.STR_DATA,ca.pos, 8000) AS COLUMN_2

FROM @sourcedata SD

CROSS APPLY (SELECT CHARINDEX('/', SD.STR_DATA, 2) ) ca(pos)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 9

WayneS (8/12/2015)
Eirikur,
Since you're doing a performance test, how does this compare?

Close to identical to the CTE version in performance and funny enough the QUERYTRACEON 8607 output tree differs only in a single word (XVAR ownership), hence the server rewrites the cross apply as a CTE.

😎

WayneS SSC Guru Points: 95460 More actions · Answer 10

Sweet. Thanks for doing that.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2