Getting string part based on some character in MSSQL

Hi,
I have one Req where I have to get the portion of the string ,like I have one e-Mail iD "shivendra.narayan@.rediffmail.com". I need only rediffmail.com now. Means to say get the string after the '@.'

PLZ help me

Thank You,
Shivendra

Quote:

Originally Posted by shivendra

Hi,
I have one Req where I have to get the portion of the string ,like I have one e-Mail iD "shivendra.narayan@.rediffmail.com". I need only rediffmail.com now. Means to say get the string after the '@.'

PLZ help me

Thank You,
Shivendra

-----------

Declare string_index int
Declare col_length int
select string_index = PATINDEX('%@.%', columnname),
col_length = length(columnname)
FROM tablename
WHERE 'Write your condition here'
Now, you can use the query below to return the
SELECT SUBSTRING(columnname, string_index, (col_length - string_index))
from tablename WHERE 'Write your condition here'

Hope this helps!!
Thanks!
Santhosh

Getting Sql Variable value

Hi, Looking for a sample in C# (ASP2.0 vwd) of getting a SqlDataSource Variable (@.uNameID) into a string Variable (string uNameID)

Any help would be appreciated!

<asp:SqlDataSource ID="SqlDataSource3" runat="server" OnSelected="SqlDataSource3_Selected" ConnectionString="<%$ ConnectionStrings:testExpressConnectionString %>"
SelectCommand="SELECT userName, userPWD FROM user_test whereuserName=@.userName ">

<SelectParameters>
<asp:controlParameter Name="userName" Direction="InputOutput" Type="String" ControlID="TextBox1" />

</SelectParameters>
</asp:SqlDataSource>
<asp:Label ID="Label2" runat="server" Text="Label" Width="258px"></asp:Label><asp:Button
ID="Button1" runat="server" Text="Button" /><asp:Label ID="Label3" runat="server"
Text="Label"></asp:Label>

code--

Protected Sub SqlDataSource3_Selected(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs)

Dim myvalue As Object = e.Command.Parameters("@.userName").Value
Label3.Text = CType(myvalue, String)

End Sub

--code

Within the selectparameter part, you can change the direction property to meet your need.

|||

Thank for the reply! Is it possible to see the code in C#? I don't know VB.

|||

protected void SqlDataSource3_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
object myvalue = e.Command.Parameters("@.userName").Value;
Label3.Text = ((string)(myvalue));
}

By the way, for learning purpose you can use this VB.NET to C# converter to convert some code snippets.

http://www.developerfusion.co.uk/utilities/convertvbtocsharp.aspx

|||

Thanks again for the help but I'm getting the error:

"Parameters is a Property' but is used like a method"

|||

bmanmike39:

Thanks again for the help but I'm getting the error:

"Parameters is a Property' but is used like a method"

C# uses [] brackets for indexers, so it should be:

object myvalue = e.Command.Parameters["@.userName"].Value;

HTH,
Ryan

|||

Now i'm getting the exception : "an sql Parameter with parameter name @.userName is not contained in this sql parameter collection"

Thanks for any help!

sql

Getting Nulls

Hi,

I have a table like this with the data

patient

Case SpeciesID

112 1

223 2

121 1

211 Null

200 2

Now I am getting the string like this from front end

@.Species='1,Null'

Now i need to get data from this table which are speciesid 1 and Null only.

My query look like this:

Declare @.Species varchar(10)

set @.species='1,Null'

select * from paPatient p

where ('%,'+@.Species+',%' like '%,'+cast(p.paSpeciesID as nvarchar(max))+',%')

I am getting only SpeciesID =1 not Cases which are having SpeciesID as null.How to get that Null cases information.

Thanks in advance.

A couple of different issue here.

You are attempting to handle @.Species as an ARRAY. SQL Server isn't capable of using an array. (There are some kludges around however.)

You are adding wildcards to each side of your field value, and by so doing will remove any possibility of using indexing.

NULL can NEVER be LIKE NULL. It is impossible to evaluate NULL. It just is.

IF you want your query to check for NULL values also, then something like this (@.Species MUST contain only one (1) value):

Code Snippet

SELECT

Col1,

Col2,

etc

FROM paPatient

WHERE ( paSpeciesID = @.Species

OR paSpecies IS NULL

)

|||

Hi,

Declare @.Species varchar(10)

set @.species='1,0'

I solved it like the following:

And ('%,'+@.Species+',%' like '%,'+cast(isnull(pp.paSpeciesID,0) as nvarchar(max))+',%')

Thanks

|||

That could work for values other than NULL values passed in the parameter.

Also, be aware, you will not be using indexes, and the query execution may be less than optimal.

|||

Hi,

From my Sqlserver reporting Services report I have the multivalued parameter from that multi valued parameter I am getting the Species value like in the format:'0,1,-1' here -1 is for if null is selected.

So I am comparing like this

@.Species='0,1,-1' this is from my report.

select * from patient pp

Where ('%,'+@.Species+',%' like '%,'+cast(isnull(pp.paSpeciesID,-1) as nvarchar(max))+',%')

Any better approach is available for this comparision.

Thanks in advance

getting length of 8 characters

Claim number (string)
CF060001
CF060001A
CF060001B
AV000001
AV000212F
AV000001F
FD232122
FD232122G
SD223213
SD223213H

I only want to get records, which have length of 8 characters.
So output will be CF060001, AV000001, FD232122, and SD223213

Anyone can help me to write this in sql?WHERE LEN(ClaimNumber) = 8

or possibly you will need

WHERE LEN(RTRIM(ClaimNumber)) = 8

Roy Harvey
Beacon Falls, CT

On 13 Dec 2006 12:55:20 -0800, "TGEAR" <ted_gear@.hotmail.comwrote:

Quote:

Originally Posted by

>Claim number (string)
>CF060001
>CF060001A
>CF060001B
>AV000001
>AV000212F
>AV000001F
>FD232122
>FD232122G
>SD223213
>SD223213H
>
>I only want to get records, which have length of 8 characters.
>So output will be CF060001, AV000001, FD232122, and SD223213
>
>Anyone can help me to write this in sql?

|||Roy Harvey (roy_harvey@.snet.net) writes:

Quote:

Originally Posted by

WHERE LEN(ClaimNumber) = 8
>
or possibly you will need
>
WHERE LEN(RTRIM(ClaimNumber)) = 8

Since len() does not count trailing blanks, rtrim is redudant here.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Wed, 13 Dec 2006 22:43:30 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>Since len() does not count trailing blanks, rtrim is redudant here.

Thanks for the education! 8-)

Roy|||Roy Harvey napisal(a):

Quote:

Originally Posted by

WHERE LEN(ClaimNumber) = 8
>
or possibly you will need
>
WHERE LEN(RTRIM(ClaimNumber)) = 8

.... or in case N data, and automaticly avoid problem with spaces:

where datalength(ClaimNumber)/2 = 8

Matik|||Matik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

Roy Harvey napisal(a):

Quote:

Originally Posted by

>WHERE LEN(ClaimNumber) = 8
>>
>or possibly you will need
>>
> WHERE LEN(RTRIM(ClaimNumber)) = 8

>
... or in case N data, and automaticly avoid problem with spaces:
>
where datalength(ClaimNumber)/2 = 8

Not really sure what you mean. len() counts characters and ignores
trailing spaces, so it is an ideal function to use in this case, as
it works the same with varchar and nvarchar data. (It does not work
with text/ntext though.)

datalength on the other hand counts bytes and includes trailing spaces,
so with datalength you need to trim and you need to know whether you
are working with varchar or nvarchar data.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx