question about query part of a string

I'm passing a variable to SQL and I want it to query a column (IP_user), but query any part of what is given. For example if I given Chris it would bring up Chris, Christian, Christine, etc. What is the syntax to do this? thanks

Try this

ALTER PROCEDURE [dbo].[SPU_XXXX]

@.SEARCH VARCHAR(30) = NULL

AS

SET @.SEARCH = '%'+ @.SEARCH + '%'

SELECT CLIENT.CLIENT, CLIENT.CLIENTID

FROM CLIENT

WHERE (CLIENT.CLIENT LIKE @.SEARCH)

|||

The best reference for this question is here:

http://www.sommarskog.se/arrays-in-sql.html

The Table of Numbers method is the easiest, fastest, etc. It will put the data into a table format that you can join to your table in a very straightforward fashion.

http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum

|||

Code Snippet

Set @.search = 'Chris'

--To get all names start with Chris

--i.e., Chrisxxxxxxxx

select * from yourtable where ip_user like @.search + '%'

--To get all names ends with Chris

--i.e., xxxxxChris

select * from yourtable where ip_user like '%' + @.search

--To get all names containes Chris at anyplace

--i.e., xxxxxxChrisxxxxxxxx or xxxxxChris or Chrisxxxxxxxx

select * from yourtable where ip_user like '%' + @.search + '%'

|||

You really should look into Fulltext, as that is its strong feature. Another option using sql engine is to use charindex() function.

e.g.

select *

from tb

where charindex(@.var,your_column)>0

|||

Thank you that was very helpful and it worked great. I have another quick question, and although this isn't technically an access forum it involves access. I have someone who runs access and with an odbc connection, connects to the sql server database. Is there any sql that can be used in accesses queries to replicate this? I apologize ahead of time for posting this here, I couldn't find an access forum...

|||In Access, mark the Query as a 'Pass Though' query, and the query as above will be sent to execute on SQL Server without Access attempting to check syntax and compile.

Question about implicit string/datetime conversion.

(SQL Server 2000, SP3a)
Hello all!
If I have a query like:
select * from MyTable where MyDateField < '10/01/2003'
Does SQL Server implicitly convert that date string to a DATETIME datatype, or is it
implicitly converting all records' MyDateField to a string datatype? Since the VARCHAR(X)
is likely longer than the 8-byte datetime field, I suspect the latter, but how could I
find out for certain?
I suppose I could eliminate the ambiguity by explicitly converting:
select * from MyTable where MyDateField < convert(datetime, '10/01/2003')
Thanks for any help you can provide!
John PetersonJohn,
It follows the precedency rule...
http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_2js5.asp?frame=true
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OHF$SjbkDHA.1084@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> If I have a query like:
> select * from MyTable where MyDateField < '10/01/2003'
> Does SQL Server implicitly convert that date string to a DATETIME
datatype, or is it
> implicitly converting all records' MyDateField to a string datatype?
Since the VARCHAR(X)
> is likely longer than the 8-byte datetime field, I suspect the latter, but
how could I
> find out for certain?
> I suppose I could eliminate the ambiguity by explicitly converting:
> select * from MyTable where MyDateField < convert(datetime, '10/01/2003')
> Thanks for any help you can provide!
> John Peterson
>|||Thanks, oj!
As I read this, that seems to suggest that the lower precedence string (varchar)
'10/01/2003' would be implicitly converted to the higher precedence datetime. In essence,
it would be equivalent to the second statement in my original post.
Thanks again for the information. I'll keep that link handy! :-)
"oj" <nospam_ojngo@.home.com> wrote in message
news:udw67nbkDHA.3700@.TK2MSFTNGP11.phx.gbl...
> John,
> It follows the precedency rule...
> http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_2js5.asp?frame=true
>
> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OHF$SjbkDHA.1084@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > If I have a query like:
> >
> > select * from MyTable where MyDateField < '10/01/2003'
> >
> > Does SQL Server implicitly convert that date string to a DATETIME
> datatype, or is it
> > implicitly converting all records' MyDateField to a string datatype?
> Since the VARCHAR(X)
> > is likely longer than the 8-byte datetime field, I suspect the latter, but
> how could I
> > find out for certain?
> >
> > I suppose I could eliminate the ambiguity by explicitly converting:
> >
> > select * from MyTable where MyDateField < convert(datetime, '10/01/2003')
> >
> > Thanks for any help you can provide!
> >
> > John Peterson
> >
> >
>|||Hi John!
Just be aware that SQL7 did it differently. SQL7 converted the "data side" to the "column side".
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"John Peterson" <j0hnp@.comcast.net> wrote in message news:udwiCubkDHA.1656@.tk2msftngp13.phx.gbl...
> Thanks, oj!
> As I read this, that seems to suggest that the lower precedence string (varchar)
> '10/01/2003' would be implicitly converted to the higher precedence datetime. In essence,
> it would be equivalent to the second statement in my original post.
> Thanks again for the information. I'll keep that link handy! :-)
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:udw67nbkDHA.3700@.TK2MSFTNGP11.phx.gbl...
> > John,
> >
> > It follows the precedency rule...
> >
> > http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_2js5.asp?frame=true
> >
> >
> > --
> > -oj
> > RAC v2.2 & QALite!
> > http://www.rac4sql.net
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:OHF$SjbkDHA.1084@.tk2msftngp13.phx.gbl...
> > > (SQL Server 2000, SP3a)
> > >
> > > Hello all!
> > >
> > > If I have a query like:
> > >
> > > select * from MyTable where MyDateField < '10/01/2003'
> > >
> > > Does SQL Server implicitly convert that date string to a DATETIME
> > datatype, or is it
> > > implicitly converting all records' MyDateField to a string datatype?
> > Since the VARCHAR(X)
> > > is likely longer than the 8-byte datetime field, I suspect the latter, but
> > how could I
> > > find out for certain?
> > >
> > > I suppose I could eliminate the ambiguity by explicitly converting:
> > >
> > > select * from MyTable where MyDateField < convert(datetime, '10/01/2003')
> > >
> > > Thanks for any help you can provide!
> > >
> > > John Peterson
> > >
> > >
> >
> >
>

Question about formatting a date

I have a date string that is an integer 20040119 and I converted it to a varchar and then to date format-CONVERT(datetime, CAST(my_date AS varchar(8))) AS my_date
My result is 2003-12-31 00:00:00.000. How can I now format this string so I don't get the time string at the end?
ThanksYou already did.|||Maybe I didn't explain good enough. I want to drop all the zeros at the end and just show the date and not the time.
Thanks|||Something like this:

select RIGHT(CONVERT(char(10), CONVERT(datetime, CAST(my_date AS varchar(8))),103),10) AS my_date|||select convert(char(10), convert(datetime, '20040119'), 101)|||Thanks alot Avatar. You left off a right parenthesis, but that was the least of my worries. It works just like I wanted. Thanks again.|||Avatar,
I don't suppose you could explain real quick how that works?
Thanks.|||Im glad its work for you..

Here is my explanation:

1) CAST(my_date AS varchar(8)):
Convert my_date from DATETIME to VARCHAR(8) because I'm asuming that the COLUMN is DATETIME.
2) CONVERT(datetime, CAST(my_date AS varchar(8))),103):
Transform the VARCHAR(8) from Step 1 to a DATETIME with a format 'dd/mm/yyyy' (103), to convert a datetime column.
3) CONVERT(char(10), CONVERT(datetime, CAST(my_date AS varchar8))),103) :
Now transform the DATETIME from Step 2 to a VARCHAR (once again) because the function RIGHT only works with VARCHARS.
4)RIGHT(CONVERT(char(10), CONVERT(datetime, CAST(my_date AS varchar8))),103),10):
Finally the RIGHT functions takes the first 10 characters of the string.

I hope I explain well. ;)|||I got everything up to the right function. I thought the right function returns the specified number of characters from the right end of the character string?
Thanks again for your help and patience!!!|||Originally posted by exdter
I got everything up to the right function. I thought the right function returns the specified number of characters from the right end of the character string?
Thanks again for your help and patience!!!

Oppss..!
Now I'm looking better.. the RIGHT function is not necessary.
The finaly version of the QUERY is like this:

SELECT (CONVERT(char(10),CONVERT(datetime, CAST(getdate() as varchar(12)) ),103))

Because the last CONVERT function transform the datetime to a char(10)... without the use of RIGHT truncate the '2002-01-19 00:00:00.000' date to a '19/01/2004'.

Ahh.. by the way:
The LEFT or RIGHT function cuts a string by the LEFT or RIGHT respectabely... In this case RIGHT('19/01/2004',10) is no use :P

What a mess.. :D|||Thanks a whole lot!!!|||...In other words, this should have worked to begin with:

select convert(char(10), convert(datetime, '20040119'), 101)

The only difference is that your data is coming (as you said) in INTEGER data type, the the function above should have looked like this:

select convert(char(10), convert(datetime, cast(20040119 as char(8))), 101)

101 - MM/DD/YYYY
103 - DD/MM/YYYY

Take your pick!|||Yeah, gotta wonder why you don't just use rdjabarov's simple conversion. Shorter is better in programming.

question about connection string

Hi all,

I am new to asp.net. A book I read teached the way to put connection string in web.config file. the file looks like this:
<configuration>
... other settings here ...
<appSettings>
<add key="DsnWroxBooksSql"
value="server=delmonte; database=WroxBooks; user id=sa; password=" />
... other settings here ...
</appSettings>
</configuration
I was just wondering what the user id "sa" and password ""is? I have seen these in many books. is it a system build in user name and password? Can I use my own user id and password instead? Also must I hardcode the password in web.config file? is there anyway I could get around it?

thank you!SA is the default administrator account for SQL Server. YOu use that when you are using mixed mode for authentication as opposed to windows.

Default password is blank, not recommended of course.

Yes you can create your own account and use it instead.

Question about connection pooling

If I have two ASP.NET applications installed on the same machine, and both are using the same connection string to access SQL Server 2005. Are they share the same pool or use separated pools?

I need to be sure that how many connections would be connected to my database server. For example, the max. connection pool size is 100, if both Web app share the same pool, then there're may be maximum 100 connections to database. It they use separated pools, then connections may grow to 200.

Hi,

per documentation: "Connections are pooled per process, per application domain, per connection string and when using integrated security, per Windows identity."
Source:http://msdn2.microsoft.com/en-us/library/8xx3tyca.aspx (using Connection Pooling)

It means your application would have separate pools, since they are different applications and therefore have their own app domain.

Another thing is that maximum connection pool size is configurable (within connectionstring), 100 is the default maximum limit.

|||

joteke:

per documentation: "Connections are pooled per process, per application domain, per connection string and when using integrated security, per Windows identity."
Source:http://msdn2.microsoft.com/en-us/library/8xx3tyca.aspx (using Connection Pooling)

It means your application would have separate pools, since they are different applications and therefore have their own app domain.

What if my ASP.NET applications all use the same application pool? In this way, are they still have separate pools?

Thank you!

|||They don't because they have different appdomains. Every ASP.NEt application has its own AppDomain. Only connections in same application, using exactly same connection string, would use the same connection pool.|||

Ok! Now I got it.

Thank your for your help :)

Question About a Check Constraint

I am attempting to write a Check Constraint on an MSSQL2k server that will only allow a string to be INSERTED or UPDATED if it is formatted correctly.

The correct format being, that the first character is uppercase [A-Z], and any trailing characters [a-z] in the word are lowercase, unless there is a space. If there is a space, the next character after the space should be uppercase [A-Z], and any trailing characters in the word will be lowercase[a-z]. Punctuation should also be allowed.

I know a function can be written to make sure that when INSERTS and UPDATES are performed, that the strings are manipulated to conform to this requirement. The problem is that I am not going to be the person that writes these functions, and I was hoping to create this constraint as an extra level of verification, but I am not even sure that this is possible.

So now the questions:
1.) Is this possible
2.) Could someone write me an example.

Thanks in adance.
-JohnDid you find a solution? If not, do you need more?