Here you go:

SELECT

b.ID1

,b.ID2

,(SELECT COUNT(DISTINCT X1) FROM #Table1 WHERE ID1=b.ID1 AND ID2=b.ID2 AND NULLIF(X1,'') IS NOT NULL) AS X1Count

...

Just to demonstrate something, consider again Lynn's example which does an implicit conversion.

DECLARE @MWBS INT, @Result CHAR(1)

SET @MWBS = 0

SELECT

@Result =

(CASE...

On the SQL side if you want any zeroes to be null you do this:

SELECT NULLIF(fooval,0) AS foonull FROM foo

If you need to validate all values you can do something...

The problem may be as simple as redefining your DECIMAL datatype conversion. Below I do the replace and ISNUMERIC check in one pass then only CAST values that meet that...

--this would be your original source table

IF OBJECT_ID('tempdb..#SourceTable') IS NOT NULL

DROP TABLE #SourceTable

CREATE TABLE #SourceTable (

[ID] INT IDENTITY(1,1) NOT NULL,

[Name] NVARCHAR(50) NULL,

PRIMARY KEY (ID))

SET IDENTITY_INSERT #SourceTable ON

INSERT INTO #SourceTable (ID,Name)

SELECT...

newbie2 (2/12/2013)

---

I tried that, but it does not seem to work when the amount of spaces vary, for example one column may be '1 north main st' and I need...

By coincidence one of my colleagues asked me this afternoon how to do a hierarchical query. So I dug out something from my toolbox. Here's a scrubbed down generic version...

At least with this sort key method you have to keep in mind...

The databases I work on usually belong to clients and already have a schema. Often these instances are full of third-party objects with all kinds of naming conventions. So even...

I was wondering about that! Thanks for doing the proof testing.

 

You have what you need right there already (except maybe a sort value to break ties...I used FTIDCalc for that). I created an artificial sort key based on your parent-child...

Sorry Sean...I didn't see that you had already answered with exactly the same idea. 😛

 

ahpitre (2/11/2013)

---

How can I use this function to search for more than 1 character? I have data that is delimited by either a comma or semicolon (,;). Need to split...

This problem of finding matches based on the column proved more difficult than I anticipated once I started peeling the onion. To make it worth the effort, I wanted it...

You can evaluate the expression by using dynamic SQL.

There's no double-pipe in SQL. Usually X || Y means X OR Y except that Y is not evaluated if X...