February 12, 2014 at 7:57 am
Hello Everyone
I am working with some data that contains string text. Not an odd type of thing. I have a column that stores different codes for items, services, etc....
This is what the service code looks like
ABC-0350
DEF-00430
I would like to remove everything to the left of the hyphen, including the hyphen, and keep the rest.
The result would be this:
0350
00430
It seems to me that I have asked a very similar question, but I cannot find it.
DECLARE @Codes TABLE
(
Codes VARCHAR(10)
)
INSERT INTO @Codes
( Codes )
VALUES ('ABC-0350'), ('DEF-00430')
SELECT * FROM @Codes
I am not the best at dealing with strings and string manipulations.
Thank you in advance for your assistance, suggestions and comments
Andrew SQLDBA
February 12, 2014 at 8:05 am
I'm certain there's better ways to do this but I'd recommend creating a function that can be called as needed - this may help:CREATE FUNCTION [dbo].[fx_SplitColumnText](
@String varchar(50),
@Delimiter varchar(5),
@Side int
) RETURNS varchar(50)
AS
/*
----------------------------------------------------------------------------------------------------------------
Purpose:Splits a string into 2 pieces, returns either side of the delimited string
----------------------------------------------------------------------------------------------------------------
NOTES:@Side = 1 gets what's to the left of the delimiter
@Side = 0 gets what's to the right of the delimiter
----------------------------------------------------------------------------------------------------------------
SELECT dbo.fx_SplitColumnText('DEF-00430', '-', 0)
SELECT dbo.fx_SplitColumnText('ABC-0350', '-', 0)
*/
BEGIN
D-ECLARE @NewDelimiter varchar(5)
D-ECLARE @strReturn NVARCHAR(50)
D-ECLARE @Pos1 int, @Len1 int
D-ECLARE @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
RETURN @strResult
END
NOTE - "D-ECLARE" has a hyphen in it to avoid issues with our proxy server...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 12, 2014 at 8:09 am
My attempt:
DECLARE @Codes TABLE
(
Codes VARCHAR(10)
)
INSERT INTO @Codes
( Codes )
VALUES ('ABC-0350'), ('DEF-00430')
SELECT codes, RIGHT(Codes, CHARINDEX('-', REVERSE(Codes))-1)
FROM @Codes
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
February 12, 2014 at 8:17 am
MyDoggieJessie (2/12/2014)
I'm certain there's better ways to do this but I'd recommend creating a function that can be called as needed - this may help:CREATE FUNCTION [dbo].[fx_SplitColumnText](NOTE - "D-ECLARE" has a hyphen in it to avoid issues with our proxy server...@String varchar(50),
@Delimiter varchar(5),
@Side int
) RETURNS varchar(50)
AS
/*
----------------------------------------------------------------------------------------------------------------
Purpose:Splits a string into 2 pieces, returns either side of the delimited string
----------------------------------------------------------------------------------------------------------------
NOTES:@Side = 1 gets what's to the left of the delimiter
@Side = 0 gets what's to the right of the delimiter
----------------------------------------------------------------------------------------------------------------
SELECT dbo.fx_SplitColumnText('DEF-00430', '-', 0)
SELECT dbo.fx_SplitColumnText('ABC-0350', '-', 0)
*/
BEGIN
D-ECLARE @NewDelimiter varchar(5)
D-ECLARE @strReturn NVARCHAR(50)
D-ECLARE @Pos1 int, @Len1 int
D-ECLARE @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
RETURN @strResult
END
Turn the above scalar function to iTVF's if you want a decent performance...
February 12, 2014 at 8:23 am
Here are two options.
DECLARE @Codes TABLE
(
Codes VARCHAR(10)
)
INSERT INTO @Codes
( Codes )
VALUES ('ABC-0350'), ('DEF-00430')
SELECT Codes,
STUFF(Codes, 1, CHARINDEX('-', Codes), ''),
SUBSTRING(Codes, CHARINDEX('-', Codes) + 1, 8000)
FROM @Codes
February 12, 2014 at 8:40 am
Thank you to Everyone.
I actually forgot to mention that this was a one time update of another column, in a table with less than 7000 rows. So I was not looking for great performance
I will keep that code handy.
Thanks again Everyone
Andrew SQLDBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply