Tuesday, September 24, 2013

[SQL Server] How to search?

[SQL Server] How to search?

How to search?

Posted: 24 Sep 2013 12:46 PM PDT

Hi Everyone ;-)I hope everyone is having a nice day ahead ;-)I have a question guys...I want to search for example a Sex whether Male or Female regardless of their status..How should i do that??...here is my Code in SQL 2005 and my Front-end is Visual Studio 2008see my UI attachment[code="plain"]set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================ALTER PROCEDURE [dbo].[SearchBiography] @firstname varchar(50), @middlename varchar(50), @lastname varchar(50), @sex varchar(50), @status varchar(50), @sexID int, @statusID intASBEGIN SET NOCOUNT ON; DECLARE @SqlQuery varchar(max) , @SqlQueryFirstName varchar(max),@SqlQueryMiddleName varchar(max), @SqlQueryLastName varchar(max), @SqlQuerySex varchar(max), @SqlQueryStatus varchar(max), @SqlSexIDQuery varchar(max), @SqlStatusIDQuery varchar(max) SET @SqlQuery = '' IF LEN(@sex) > 0 SET @SqlQuerySex = ' sex = ''' + @sex + '''' ELSE SET @SqlQuerySex = '' IF LEN(@status) > 0 SET @SqlQueryStatus = ' AND status = ''' + @status + '''' ELSE SET @SqlQueryStatus = '' IF @sexID <> 0 SET @SqlSexIDQuery = ' AND sexID IN(SELECT sexID FROM sex WHERE sexID = ' + convert(varchar(20), @sexID) + ' )' ELSE SET @SqlSexIDQuery = '' IF @statusID <> 0 SET @SqlStatusIDQuery = ' AND statusID IN(SELECT statusID FROM status WHERE statusID = ' + convert(varchar(20), @statusID)+ ')' ELSE SET @SqlStatusIDQuery= '' IF LEN(@firstname) > 0 SET @SqlQueryFirstName = ' AND firstname like ''%' + @firstname + '%''' ELSE SET @SqlQueryFirstName = '' IF LEN(@middlename) > 0 SET @SqlQueryMiddleName = ' AND middlename like ''%' + @middlename + '%''' ELSE SET @SqlQueryMiddleName = '' IF LEN(@lastname) > 0 SET @SqlQueryLastName =' AND lastname like ''%' + @lastname + '%''' ELSE SET @SqlQueryLastName = '' SET @SqlQuery = 'SELECT * FROM TestMyView WHERE ' SET @SqlQuery = @SqlQuery + @SqlQuerySex + @SqlQueryStatus + @SqlSexIDQuery + @SqlStatusIDQuery + @SqlQueryFirstName + @SqlQueryMiddleName + @SqlQueryLastName EXEC(@SqlQuery) PRINT(@SqlQuery)END[/code]

Breaking up a string of text

Posted: 24 Sep 2013 05:20 AM PDT

Hello again,I have some very dirty data here. I'm working on a Customers table which I plan to clean up and insert into a new table. Currently, the table looks like this:LastName, FirstName, MiddleInt-----------------------------------------Smith,John,Q * *So "Smith,John,Q" IS the last name and "*" are stored in the First Name and Middle Int fields.Yuk.I am able to just select the last name based on the comma like so:[font="Courier New"]LEFT(LastName,(CHARINDEX(',',LastName))) AS LastNameOnly[/font]However, I am having trouble pulling out the first name. Hell, at this point, I'd be fine with it if first name contained any possible Middle Int. I'd be fine if FirstName was "John,Q". I can clean that up in a second step. I'd be grateful if someone could help just getting the "First Name" out of the string. I've tried various methods of SUBSTRING, LEN, and what not. Just can't seem to connect the dots....Thanks

Creating a new column and inserting data on it from an existing column

Posted: 23 Sep 2013 03:51 PM PDT

Is there any way we can create a new column based on the results of a select. i am trying to create a new column that would store users address that is currently stored in his name as name (nvarchar 500) "this,guy (Chicago)" into three new columns as firstname lastname address guy this newyorkthanks,

sql server 2008 r2 installation error

Posted: 23 Sep 2013 08:18 PM PDT

hi while installingsql server 2008 r2 at the end its giving error service 'sql browser' start errorand installation failing. please can anybody help me I have tried 3 to 4 timesI have searched the net they said use default instance nothing workingos:windows 7 home premiumregardssudarshan
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog