Need Records from strings

Question

Post reply

Need Records from strings

kiran.rajenimbalkar

SSCrazy

Points: 2603
More actions
March 23, 2011 at 8:04 am

#233762

Hi,
I have some records which need to be subtracted.
Please see the below sql and provide me the solution
Create Table Script
CREATE TABLE abc_tmp
(Path nvarchar(850))
Insert Records
insert into abc_tmp values('rptclientrifvdistrictgrantstatuschangelog_MillmanR_9dfb3dfa-081b-48d1-8bbf-81361bbd9cb9')
insert into abc_tmp values('rptclientrifvdistrictgrantstatuschangelog_MeiA_e472f742-c6a3-4a70-822e-1c5417efb293')
insert into abc_tmp values ('rptclientrifvglobalgrantproposalbytrackingnumber_BateyK_23eafa59-e222-462d-a08c-27ed8131c274')
I want records from the strings as below,
rptclientrifvdistrictgrantstatuschangelog
rptclientrifvdistrictgrantstatuschangelog
rptclientrifvglobalgrantproposalbytrackingnumber
I dont know the exact length for the string.
Best Regards,
Kiran

Viewing 13 posts - 1 through 12 (of 12 total)

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

olibbhq SSCarpal Tunnel Points: 4034 More actions · Answer 1

hi,

use this

http://msdn.microsoft.com/en-us/library/ms187748.aspx

to build up you lengths and then use left() and right() as required. Reverse() can also be really useful.

Cheers,

Oliver

Dan.Humphries SSChampion Points: 12125 More actions · Answer 2

I would advise looking up how to do a substring. If the name you need is sonstant you can grab a set number of characters. There is a few ways you can do this you could for example grab the left 15 charachters or you could substring from position 5 through 10.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

May I ask how you ended up with delimited strings in a column? How is this data entering the system? Tools like BCP, BULK INSERT and SSIS are designed to parse delimited data and load it into separate columns from the outset.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

MyDoggieJessie SSC-Forever Points: 44461 More actions · Answer 4

You could try this:

Usage:

SELECT dbo.fx_SplitColumnText('rptclientrifvdistrictgrantstatuschangelog_MillmanR_9dfb3dfa-081b-48d1-8bbf-81361bbd9cb9', '_', 1)

Returns:

rptclientrifvdistrictgrantstatuschangelog

TSQL:

(You'll have to format this yourself)

USE [F1Settings]

GO

/****** Object: UserDefinedFunction [dbo].[fx_SplitColumnText] Script Date: 03/23/2011 13:40:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/* ############################################################################################################### */

--SPLITS TEXT INTO SEPERATE STRINGS

/* ############################################################################################################### */

ALTER FUNCTION [dbo].[fx_SplitColumnText](

@String varchar(500),

@Delimiter varchar(5),

@Side int

) RETURNS varchar(50)

AS

/*

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

Purpose:Splits a string into 2 pieces, returns either side of the delimited string

Department:###

Created For:###

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

NOTES:@Side = 1 gets what's to the left of the delimiter

@Side = 0 gets what's to the right of the delimiter

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

Created On:12/10/2006

Create By:MyDoggieJessie

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

Modified On:

Modified By:

Changes:

1.

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

SELECT dbo.fx_SplitColumnText('rptclientrifvdistrictgrantstatuschangelog_MillmanR_9dfb3dfa-081b-48d1-8bbf-81361bbd9cb9', '_', 1)

*/

BEGIN

/* ######################################### START MAIN PROCEDURE HERE ########################################## */

DECLARE @NewDelimiter varchar(5)

DECLARE @strReturn NVARCHAR(50)

DECLARE @Pos1 int, @Len1 int

DECLARE @strResult varchar(50)

SET @NewDelimiter = '^'

SET @strReturn = REPLACE(REPLACE(@String,

LTRIM(RTRIM(@Delimiter)), '^'),' ', '')

SET @Len1 = LEN(@strReturn)

SET @Pos1 = CHARINDEX(@NewDelimiter,@strReturn)

IF (@Side = 1)

BEGIN

SET @strResult = LEFT(@strReturn, @Pos1-1)

END

ELSE

BEGIN

SET @strResult = RIGHT(@strReturn, @Len1 - @Pos1)

END

/* ########################################## END MAIN Function HERE ########################################### */

RETURN @strResult

/*

SELECT dbo.fx_SplitColumnText('10622.97 to 8000', 'to', 1)

*/

END

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 5

Try this,

CHARINDEX will help u

CREATE TABLE #abc_tmp

(Path nvarchar(850))

insert into #abc_tmp values('rptclientrifvdistrictgrantstatuschangelog_MillmanR_9dfb3dfa-081b-48d1-8bbf-81361bbd9cb9')

insert into #abc_tmp values('rptclientrifvdistrictgrantstatuschangelog_MeiA_e472f742-c6a3-4a70-822e-1c5417efb293')

insert into #abc_tmp values ('rptclientrifvglobalgrantproposalbytrackingnumber_BateyK_23eafa59-e222-462d-a08c-27ed8131c274')

Select SUBSTRING(Path,0, CHARINDEX('_',Path) ), * from #abc_tmp

DROP TABLE #abc_tmp

Thanks
Parthi

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

parthi-1705 (3/23/2011)
Try this,
CHARINDEX will help u
CREATE TABLE #abc_tmp
(Path nvarchar(850))
insert into #abc_tmp values('rptclientrifvdistrictgrantstatuschangelog_MillmanR_9dfb3dfa-081b-48d1-8bbf-81361bbd9cb9')
insert into #abc_tmp values('rptclientrifvdistrictgrantstatuschangelog_MeiA_e472f742-c6a3-4a70-822e-1c5417efb293')
insert into #abc_tmp values ('rptclientrifvglobalgrantproposalbytrackingnumber_BateyK_23eafa59-e222-462d-a08c-27ed8131c274')
Select SUBSTRING(Path,0, CHARINDEX('_',Path) ), * from #abc_tmp
DROP TABLE #abc_tmp

VERY clever use of "0" for the second operand to eliminate the trailing delimiter, Parthi. 🙂 Most folks don't know that operand can be less than "1". 🙂

--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)
Intro to Tally Tables and Functions

UMG Developer SSChampion Points: 13482 More actions · Answer 7

That is an interesting use of the start position, but it looks to me like either the documentation in BOL is wrong, or the behavior is a bug. BOL says: http://msdn.microsoft.com/en-us/library/ms187748.aspx

start_expression
Is an integer or bigint expression that specifies where the returned characters start. If start_expression is less than 1, the returned expression will begin at the first character that is specified in value_expression. In this case, the number of characters that are returned is the largest value of either the sum of start_expression and length_expression or 0. If start_expression is greater than the number of characters in the value expression, a zero-length expression is returned.

In this example:

SELECT SUBSTRING('Test', 0, 2);

It returns just "T", but the sum of start_expression and length_expression is 2, so it should return "Te". Or am I interpreting it wrong?

srikant maurya Hall of Fame Points: 3293 More actions · Answer 8

You can also try below query to get result

select stuff(path,charindex('_',path),len(path),'')

from abc_tmp

kiran.rajenimbalkar SSCrazy Points: 2603 More actions · Answer 9

kiran.rajenimbalkar

SSCrazy

Points: 2603

March 24, 2011 at 1:30 am

#1302805

Hi,

It's worked.

Thanks for help.

Best Regards,

Kiran R

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

UMG Developer (3/23/2011)
That is an interesting use of the start position, but it looks to me like either the documentation in BOL is wrong, or the behavior is a bug. BOL says: http://msdn.microsoft.com/en-us/library/ms187748.aspx
start_expression
Is an integer or bigint expression that specifies where the returned characters start. If start_expression is less than 1, the returned expression will begin at the first character that is specified in value_expression. In this case, the number of characters that are returned is the largest value of either the sum of start_expression and length_expression or 0. If start_expression is greater than the number of characters in the value expression, a zero-length expression is returned.
In this example:
SELECT SUBSTRING('Test', 0, 2);
It returns just "T", but the sum of start_expression and length_expression is 2, so it should return "Te". Or am I interpreting it wrong?

I think that whoever wrote the BOL article simply forgot about 0 and that it actually is the first "position" to the left of the string. I believe that BOL should say:

In this case, the number of characters that are returned is the largest value of either the sum of start_expression and length_expression minus 1 or 0.

If you try other <1 values for the start position on longer strings, you'll see why.

--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)
Intro to Tally Tables and Functions

WayneS SSC Guru Points: 95405 More actions · Answer 11

Jeff Moden (3/23/2011)
parthi-1705 (3/23/2011)
Try this,
CHARINDEX will help u
CREATE TABLE #abc_tmp
(Path nvarchar(850))
insert into #abc_tmp values('rptclientrifvdistrictgrantstatuschangelog_MillmanR_9dfb3dfa-081b-48d1-8bbf-81361bbd9cb9')
insert into #abc_tmp values('rptclientrifvdistrictgrantstatuschangelog_MeiA_e472f742-c6a3-4a70-822e-1c5417efb293')
insert into #abc_tmp values ('rptclientrifvglobalgrantproposalbytrackingnumber_BateyK_23eafa59-e222-462d-a08c-27ed8131c274')
Select SUBSTRING(Path,0, CHARINDEX('_',Path) ), * from #abc_tmp
DROP TABLE #abc_tmp
VERY clever use of "0" for the second operand to eliminate the trailing delimiter, Parthi. 🙂 Most folks don't know that operand can be less than "1". 🙂

This is the second subtle T-SQL thing I've learned today 🙂 - today is looking like a very good day indeed! 😀

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

UMG Developer SSChampion Points: 13482 More actions · Answer 12

Jeff Moden (3/24/2011)
I think that whoever wrote the BOL article simply forgot about 0 and that it actually is the first "position" to the left of the string. I believe that BOL should say:
In this case, the number of characters that are returned is the largest value of either the sum of start_expression and length_expression minus 1 or 0.

I agree, otherwise 1 and 0 would mean the same thing. But it is possible that they wanted that behavior to make it easier to use/read. Say you want to reduce the number of characters returned by 6 put -6 in the starting position. As it is now you have to use -5 which is less intuitive.