.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

patindex using in substring

Posted By:      Posted Date: September 27, 2010    Points: 0   Category :Sql Server



PATINDEX('%HP D%',Substring(e.ErrorString,Charindex

View Complete Post

More Related Resource Links

Use of PATINDEX function in select clause With Linq

hello       i have an problem to use of PATINDEX function in select clause with Linq.      if anyone have any clue about it then please update me its urgent.Thanks In Advance.


Hi All, We are having string in the format of:- 'Xyz, Abc P.' 'Pqr, Lmn Q.' We need to split the the string and store into three different columns Expected O/p:- FirstName LastName MiddleName Xyz Abc P Pqr Lmn Q Thanks Regards, Kumar

Apply substring to date field

I have a gridview that has a field called TargetDate which is formatted as dd/mm/yyyy.  I want to substring the numbers out of the date.  So basically remove the '/'.  How do I do that?  I can't just substring(0,2) because sometimes the date is single digits, sometimes 2 digits.  I am using vb.net.   thanks  

using substring


in asp.net i get a string frm database in  a variable

frm that i need to check for a substring which exixst in another variable ,

the length of string substring  is not knwn

eg var1="hello.howr u.what"


var2 is a substring of var1 .

how do i check



Substring not working as expected in UDF


Apologies if this is very simple and part of the basic SQL training but i'm very new to User Defined Functions (SQL 2000)

I am trying to create a function to take two variables, a varchar financial year value and an nvarchar financial month value and convert this to a datetime format, with the date automatcially being the 1st of that month.

Example: Take '2010/2011' and 2 (2nd financial month) as variables and convert to 2010-05-01 00:00:00.000

The function i am trying to create is below and i know that the code inside the function works fine, as i've tested it. The problem seems to be that the substring function is not working as it should be within the udf i am trying to create. instead of returning 4 characters, its only returning 1. Is this a known issue with UDF, and if so, is there anyway around this?


CREATE FUNCTION FinYrDate (@FinYr varchar, @FinYr_Mnth nvarchar)
RETURNS datetime

DECLARE @ReturnedDate datetime

SET @ReturnedDate =
WHEN @FinYr_Mnth < 4 THEN convert(datetime,(cast((@FinYr_Mnth + 3) as varchar) + '-1-' + SUBSTRING(@FinYr,1,4)))
WHEN @FinYr_Mnth between 4 and 9 THEN convert(datetime,(cast((@FinYr_Mnth + 3) as varchar) + '-1-' + SUBSTRING(@FinYr,6,4)))
ELSE convert

need proper substring


Table 1

col1 varchar(20)





I want to select currency after first 5 digits ,I know we can write select statement like this

select substring(col1,6,20) as currency from table 1

but I want some code which seperate currency directly only .If you know please let e know.Thanks



SUBSTRING within OLE DB Source sql vs Derived Column


One requirement of a project I'm working on is that the value within a column of my source be truncated to 30 characters.  The source column is currently a length of 50.  The previous developer was selecting the column, then downstream, used a derived column to substring the value into a new column.  The data set within this package is quite large and I'm attempting to clean it up.  The full 50 length value is never used (other than for the source of the 30 length derived column).

The question - is it good, bad, or indifferent practice to do this SUBSTRING from within the OLE DB Source SQL Command right up front, rather than passing the entire length down to a derived column?  I would assume, although maybe not eloquent possibly lending itself to more difficult debugging, it would cut down on the amount of data in the pipeline and negate the need for a derived column transformation entirely, which I assume would cut down on process time.

I realize both would work - just curious about best practice/performance impacts.



remove a substring




Is it possible to remove a part of a string between 2 other strings?

I have a large block of text that my program works with, but I need to remove all

comments that start with "<!--" and end with  "-->".


I tried an online regular expression builder, but that didn't work at all.

Is there a .NET solution that would work?


dynamic sql substring problem



i have the following transact sql which is dynamic

but when i add substring function to it to get the first 50 characters of message field i get  error

IF EXISTS (SELECT * FROM sysobjects   WHERE name = 'Messages'   AND type = 'P')

@UserID int,
@mailbox varchar(50)
DECLARE @SQL nvarchar(1000)

select @SQL= 'SELECT substring(message,1,50), I.msgID,I.subject FROM inbox I left outer join users U on U.userid=I.fromUser where '

EXECUTE sp_executesql @SQL

Invalid length parameter passed to the SUBSTRING function


Hi all,

I am having a weird issue after we upgraded our DB server to SQL 2005.

I have a SP used to extract exchange rate, and a job calls this SP daily. This job worked fine on SQL 2000, and works very well in Management studio if I call this SP seperately, but failed in sql job in 2005.

The error statement pointed to:

select left(@row, charindex(',', @row)-1),  REVERSE(left(@reversedrow, charindex(',', @reversedrow)-1))

The error message is:

Invalid length parameter passed to the SUBSTRING function.

Anyone knows what's the difference for LEFT function between sql 2000 and 2005?


SubString Problem


Hi all,

In a string I need to find the first blank space that  occurs after a '@' symbol has occured in a string. I'm having a problem finding that space.

At the moment I've hard-coded the number 10 into my code, I need to find the endindex in my example below please

        int startindex = 0;
        int endindex = 0;

        string myString = "@BankSimple We're Hiring: Director of Customer Relations";

        if (myString.IndexOf("@") != -1)
            startindex = myString.IndexOf("@") + 1;
            endindex = myString.IndexOf(" ") + 1; //problem on this line

 url = myString.Substring(startindex, 10); myString = myString.Replace(url, "<a target='_blank' href='http://www.twitter.com/" + url + "'>" + url + "</a>"); Response.Write(myString); }


Getting SubString in WebMatrix



In my WebMatrix app (.cshtml), I have a string and I want to extract a subsitring from it. I am using string.SubString(0,5) method but I am getting following error:

'string' does not contain a definition for 'SubString' and no extension method 'SubString' accepting a first argument of type 'string' could be found (are you missing a using directive or an assembly reference?)

I have included all required assemblies. Can I have suggestions please?.

Check for a substring in a string


I have a string US\jim.hover  How to check if US\ exist in that string ?

Find ISBN 10 or ISBN 13 substring RegEx.Match


Hi, i am trying to find an ISBN substring:

Regex regex = new Regex(@"ISBN(-1(?:(0)|3))?:?\x20+(?(1)(?(2)(?:(?=.{13}$)\d{1,5}([ -])\d{1,7}\3\d{1,6}\3(?:\d|x)$)|(?:(?=.{17}$)97(?:8|9)([ -])\d{1,5}\4\d{1,7}\4\d{1,6}\4\d$))|(?(.{13}$)(?:\d{1,5}([ -])\d{1,7}\5\d{1,6}\5(?:\d|x)$)|(?:(?=.{17}$)97(?:8|9)([ -])\d{1,5}\6\d{1,7}\6\d{1,6}\6\d$)))");

Match m = regex.Match("one two ISBN-13: 978-0-470-04673-9 three");

I got the regex from regexlib.com and understand it should match either on a 10 or 13 digit ISBN. Unfortunatly match always returns false.

I would also like it to be able to find the ISBN even if there are no spaces either side, i.e. "one twoISBN-13: 978-0-470-04673-9three" if that is possible.

So far not working, will take another look tomorrow (is 3am now!) but in the meantime if anyone has any pointers would be muchh appreciated.


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend