June 17, 2010 at 4:50 am
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
June 17, 2010 at 5:19 am
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
June 17, 2010 at 5:23 am
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
June 17, 2010 at 5:30 am
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
June 17, 2010 at 5:45 am
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
June 17, 2010 at 6:06 am
-- 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
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply