Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2006
    Posts
    35

    Unanswered: LIKE query using nvarchar(MAX) field

    This don't work. The YeaVoters field is an nvarchar(MAX) field that contains a list of names separated by the ";" character. It runs okay, but returns only null fields. Any suggestions? Thanks.

    SELECT SID, ActionTaken, vDate, Group
    FROM dbo.RollCalls
    WHERE (SID = 43) AND (YeaVoters LIKE N'smith')

  2. #2
    Join Date
    Dec 2006
    Posts
    35
    Nevermind. This seems to work: LIKE '%' + dbo.Members.AlphaShort + '%'

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    The YeaVoters field is an nvarchar(MAX) field that contains a list of names separated by the ";" character.
    You should consider normalising your data model.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Dec 2006
    Posts
    35
    I have no ideas what that even means...

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Very probably the best "Quick and Dirty" explanation of Database Normalization that I've ever seen.

    A table with a "delimited string" of values like YeaVoters in your example violates First Normal Form.

    Normalization is kind of like chess, it takes an afternoon to learn and a lifetime to master. Taking the afternoon is critical to your long term success with relational data in any programming/data processing/database management/etc. If your data isn't normalized, it will quickly become a management nightmare for you (like YeaVoters is starting to become already).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by Pat Phelan View Post
    If your data isn't normalized, it will quickly become a management nightmare for you (like YeaVoters is starting to become already).
    He's right, you know.

    A relational database is good at handling normalised data. If you store your data in a denormalised way, like storing all Yea voters in one column, getting information out of your system will become increasingly difficult to build and maintain (and slow) with each new requirement.

    You will have to know SQL like a boss to know all people who have voted Yea on one thing and Nay on another item.
    While, if your database had been normalised, you would only have to know SQL as the best friend of the nephew of the assistant of the boss.
    See the difference?

    If you're working with databases you MUST know normalisation, or your life will become a hell. It's not hard to learn, but you need to know it.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Dec 2011
    Posts
    27
    Code:
    If you're working with databases you MUST know normalisation, or your life will become a hell. It's not hard to learn, but you need to know it.
    I am new to the database works.i never thought normalisation is havingf this much importance. Could anyone please suggest me some effective tutorial for learning normalisation.
    Thanks in advance

  8. #8
    Join Date
    Dec 2006
    Posts
    35
    Thanks, Pat and Wim. I think my tables are pretty much normalized and I do take the time to ensure that tables are about one "thing" for the most part. Let me ask you this: I have a table of people by name (Members) and I want to associate each person in that table with with each record in YeaVoters when that person votes yea. The YeaVoters column is a text column where I simply copy and paste the list of all yeavoters from the PDF source document. I'm using LIKE '%' + dbo.Members.AlphaShort + '%' to match YeaVoters to Members. (Same for NayVoters in a separate view.) Is this a reliable method, in your opinion? It seems to be working fine on a small scale for testing. Thanks.

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I think my tables are pretty much normalized and I do take the time to ensure that tables are about one "thing" for the most part.
    Then you have a database that is normalised for the most part. And the small part that is not normalised is where you will spend 80 % of your time on, trying to get something in it or out of it.

    I have a table of people by name (Members) and I want to associate each person in that table with with each record in YeaVoters when that person votes yea.
    With only knowing what you have told us so far, this is how I would model this part of your system. (I'm making a lot of guesses).
    Code:
    -- normalised data model
    CREATE TABLE dbo.RollCalls(
    	SID		INT	IDENTITY(1, 1)	NOT NULL,
    	ActionTaken	BIT	NOT NULL,
    	vDate		DATE	NOT NULL, 
    	Group_		VARCHAR(10)	NOT NULL,
    	CONSTRAINT pk_RollCalls PRIMARY KEY(SID)
    )
    
    CREATE TABLE dbo.Votes(
    	SID		INT	NOT NULL,
    	VoterId		INT	NOT NULL,
    	YeaNayVote	CHAR(1)	NOT NULL
    		CONSTRAINT CC_votes_YeaNayVote CHECK(YeaNayVote IN ('Y', 'N', '-')),
    	CONSTRAINT pk_Votes PRIMARY KEY(SID, VoterId)
    )
    
    CREATE TABLE dbo.Members(
    	MemberId	INT	IDENTITY(1, 1)	NOT NULL,
    	Name	NVARCHAR(100)	NOT NULL,
    	CONSTRAINT pk_member PRIMARY KEY(MemberId)
    )
    
    - define a Foreign Key between the Votes and the Members who voted
    ALTER TABLE dbo.Votes
    	ADD CONSTRAINT fk_Votes_People FOREIGN KEY (VoterId)
    		REFERENCES dbo.Member (MemberId)
    You should also specify a Foreign Key between the Votes and the RollCalls table.

    Code:
    -- your original question answered 
    SELECT RollCalls.SID, ActionTaken, vDate, Group_ 
    FROM dbo.RollCalls 
    	INNER JOIN dbo.votes ON
    		RollCalls.SID = votes.SID
    	INNER JOIN dbo.People ON
    		Votes.VoterId = People.PersonId
    WHERE SID = 43 AND
    	YeaNayVote = 'Y' AND
    	People.Name = N'Smith'
    Imagine someone asks you to deliver these numbers: How many votes were Yay, Nay or abstentions for bill 43 and how many votes were brought out in total?
    Code:
    SELECT SID,
    	SUM(CASE WHEN YeaNayVote = 'Y' THEN 1 ELSE 0 END) as YeaVotes,
    	SUM(CASE WHEN YeaNayVote = 'N' THEN 1 ELSE 0 END) as NayVotes,
    	SUM(CASE WHEN YeaNayVote = '-' THEN 1 ELSE 0 END) as abstentions,
    	COUNT(*) as TotalVotes
    FROM dbo.RollCalls 
    	INNER JOIN dbo.votes ON
    		RollCalls.SID = votes.SID
    WHERE RollCalls.SID = 43
    Now suppose you have to extract that data from your original table.

    The YeaVoters column is a text column where I simply copy and paste the list of all yeavoters from the PDF source document. I'm using LIKE '%' + dbo.Members.AlphaShort + '%' to match YeaVoters to Members. (Same for NayVoters in a separate view.) Is this a reliable method, in your opinion?
    Please define "reliable".
    If you mean: "It will reliably get me frustrated to the point where I throw my PC out of the window and my boss and my users will reliable hate me after a while because it takes me ages to get an answer out of the system for even the simplest of their questions", then my answer is Yes!

    It seems to be working fine on a small scale for testing.
    Yep, as long as it is for the votes in your bowling club, it will be fast enough. If you're working in Congress or in a small town's cityhall, it will fail.
    Last edited by Wim; 12-20-11 at 20:12.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    Join Date
    Dec 2006
    Posts
    35
    Thanks for all of this. I am testing my query to see if it can handle the volume without using my old join query. That worked great (joined members and their yeas and nays by adding those records to a separate table), but this new way would save me an incredible amount of time.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •