to find string between special characters appearing twice in a string

Question

Post reply

to find string between special characters appearing twice in a string

lijusmail

Default port

Points: 1486
More actions
June 17, 2010 at 4:50 am

#222209

Hi
I have been searching the forum to find some details about a string functions
I have a column which has values is 00_MECH_UG,00_PRE_LG
I would like to have a query which gives me MECH,PRE etc...from the column.
Help much appreciated.
thanks
Liju

Viewing 6 posts - 1 through 5 (of 5 total)

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Are you just trying to strip the numbers/special characters?

If so: -

DECLARE @input AS VARCHAR(50)

SET @input = '00_MECH_UG,00_PRE_LG'

SELECT @input

DECLARE @output VARCHAR(50)

SET @output = ''

DECLARE @length INT

SET @length = Len(@input)

DECLARE @count INT

SET @count = 1

WHILE @count <= @length

BEGIN

DECLARE @check INT

SET @check = Ascii(Substring(@input, @count, 1))

IF @check BETWEEN 65 AND 90

OR @check BETWEEN 97 AND 122

SET @output = @output + CHAR(@check)

SET @count = @count + 1

END

SELECT @output

Ouput -

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

MECHUGPRELG

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

lijusmail Default port Points: 1486 More actions · Answer 2

Hi

Oops i think i was not very clear

I have a column which has the following values

00_MECH_UG

00_PRE_LW

EN_CVLG_UG

I would like to have the result as

MECH

PRE

CVLG

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

Is it always three characters before the part that you're interested in? And always three characters after it?

e.g. XX_wantthis ?

If so, simple way to do it is: -

--First, lets build some test data

DECLARE @table AS TABLE(

somecolumn VARCHAR(50))

INSERT INTO @table(somecolumn)

SELECT '00_MECH_UG'

UNION ALL SELECT '00_PRE_LW'

UNION ALL SELECT 'EN_CVLG_UG'

--Simple way

SELECT LEFT(RIGHT(somecolumn,LEN(somecolumn)-3),LEN(somecolumn)-6) FROM @table

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

MeThinks SSC Eights! Points: 851 More actions · Answer 4

ALTER FUNCTION [dbo].[StrBetween] (@InputStr varchar(MAX), @StrStart Varchar(255), @StrStop Varchar(255))

RETURNS VARCHAR(MAX)

AS

BEGIN

DECLARE @STR VARCHAR(MAX),

@START INT, @STOP INT

SET @START = CHARINDEX(@StrStart, @INPUTSTR)

IF @START>0

SET @INPUTSTR = SUBSTRING(@INPUTSTR, @START+DATALENGTH(@StrStart), 2147483647)

SET @STOP = CHARINDEX(@StrStop, @INPUTSTR)-1

IF @STOP<0 SET @STOP=DATALENGTH(@INPUTSTR)

SET @STR = SUBSTRING(@INPUTSTR, 1, @STOP)

RETURN @STR

END

GO

-- Usage

select dbo.StrBetween('00_MCHE_ddd', '_', '_')

-- returns MCHE

--OR

select ID, dbo.StrBetween(Column, '_', '_')

FROM SomeTable

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

-- query 1

SELECT

MyString,

--CHARINDEX('_', MyString),

--SUBSTRING(MyString, CHARINDEX('_', MyString)+1, LEN(MyString)),

--CHARINDEX('_', SUBSTRING(MyString, CHARINDEX('_', MyString)+1, LEN(MyString))),

MySubstring = LEFT(SUBSTRING(MyString, CHARINDEX('_', MyString)+1, LEN(MyString)),

CHARINDEX('_', SUBSTRING(MyString, CHARINDEX('_', MyString)+1, LEN(MyString)))-1)

FROM ( -- sample data

SELECT '00_MECH_UG' AS MyString UNION ALL

SELECT '00_PRE_LW' UNION ALL

SELECT 'EN_CVLG_UG'

) s

-- query 2

SELECT MyString,

MySubstring = SUBSTRING(MyString, StartPos, Endpos-StartPos+1)

FROM ( -- sample data

SELECT MyString,

StartPos = CHARINDEX('_', MyString)+1,

Endpos = LEN(MyString)-CHARINDEX('_', REVERSE(MyString))

FROM (

SELECT '00_MECH_UG' AS MyString UNION ALL

SELECT '00_PRE_LW' UNION ALL

SELECT 'EN_CVLG_UG'

) s

) d

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden