MID Function SQL
-
Hi,
I need to perform the equivalent of a MID function (vb.. Access etc)
The string is something like 1234,666,12345,12345 inside of varchar field.
I need the string 666.
I can find the location of the first and second comma... but for the life of me, I can't figure out the solution required to return the string 666 from the field.
The string 666 is an example only, I do need to return the string in the second column of this string.
Can anyone advise?
-
SUBSTRING() function is equivalent to Mid()
-
Hi,
The third param of substring is LENGTH.
I don't know the length of my 666 string, is usually 3 characters, but could be 4 or 5.
I should have mentioned that.
-
my brain engaged finally:
RIGHT(
LEFT(SB_CHART_OF_ACC_CODE, CHARINDEX(',',SB_CHART_OF_ACC_CODE, CHARINDEX(',',SB_CHART_OF_ACC_CODE)+1)-1)
,
CHARINDEX(',',SB_CHART_OF_ACC_CODE, CHARINDEX(',',SB_CHART_OF_ACC_CODE)+1)
-
CHARINDEX(',',SB_CHART_OF_ACC_CODE)
-1
)
-
Given the complexity of the expression, you might consider creating a UDF for it. In a UDF, you can assign results of the 1st CHARINDEX() to a locally scoped variable, which makes the overall expression much cleaner. And then in your SQL that is performing the parsing, calling a meaningful named function will be much more maintainable 3 months from now when you return to this code and think "What the @#&* is this doing ..."
-
Old programmers trick for remembering what code does... add some comments!
That, not-with-standing, I agree with PW... a UDF certainly seems like the thing to do here...--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 -
A UDF might look something like this:
CREATE FUNCTION dbo.ExtractString(@Input VARCHAR(8000))
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @pos TINYINT
DECLARE @pos1 TINYINT
SET @pos = CHARINDEX(',',@Input,1)
SET @pos1 = CHARINDEX(',',@Input,@pos+1)
SET @Input = SUBSTRING(@Input,@pos+1, @pos1-@pos-1)
RETURN @Input
END
GO
SELECT dbo.ExtractString('1234,666,12345,12345 ')
DROP FUNCTION dbo.ExtractString
GO
----------
666
(1 row(s) affected)
If you need to extract all strings this might be better:
DECLARE @strComma VARCHAR(1000)
SET @strComma = '1234,666,12345,12345'
SELECT
CAST(RIGHT(LEFT(@strComma,Number-1)
, CHARINDEX(',',REVERSE(LEFT(','+@strComma,Number-1)))) AS CHAR(30))
FROM
master..spt_values
WHERE
Type = 'P' AND Number BETWEEN 1 AND LEN(@strComma)+1
AND
(SUBSTRING(@strComma,Number,1) = ',' OR SUBSTRING(@strComma,Number,1) = '')
------------------------------
1234
666
12345
12345
(4 row(s) affected)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
here is a generalized function (we often need to extract a specific comma delimited segment) which allows you to specify the 1st, 2nd etc comma segment:
CREATE FUNCTION [dbo].[parsecomma] (@fld varchar (100), @pos int )
/* parses the comma separated string, returns the segment indicated by @pos
if none to be returned, returns null */
RETURNS varchar(20) AS
BEGIN
declare @p1 int;
declare @p2 int;
declare @CT int;
if @fld is null
return null
set @p1=dbo.commapos(@fld,@pos-1)
set @p2=dbo.commapos(@fld,@pos)
if @p1=0 and @p2=0
begin
if @pos=1
return @fld
else
return null
end
if @p1=0 return dbo.nozero(left(@fld,@p2-1)) -- first substring
if @p2=0 return dbo.noZero(right(@fld, len(@fld)-@p1)) --return last substring
return dbo.noZero(substring(@fld,@p1+1,@p2-@p1-1))
END
--support function
CREATE FUNCTION [dbo].[noZero] (@str varchar(50))
--strips out redundant leading spaces, returns net string (or null if zero length results)
RETURNS varchar(50) AS
BEGIN
set @STR=ltrim(@str)
if len(@str)=0
return null
return @STR
END
...
-- FORTRAN manual for Xerox Computers --
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply