You're welcome;-)

Quick question, would you ever have more than two rows for a subject (CanNum)?

😎

Here is a simple solution which uses charindex for the matching and a combination of len and replace to count the expected number of matches, should be enough to get...

Quick self-join type solution

😎

create table #temp(candId int identity(1,1),CanNum int,name varchar(50),Attempt1 int,Attempt2 int,attempt3 int)

insert into #temp values ( 1001, 'Abhas', 120, 150, 180)

insert into #temp values (1002, 'John', 150, 150, 180)

insert...

Here are two snips, should help get you passed this hurdle

😎

Snip 1, using logical operators instead of a case statement, works in SQLite but not in TSQL

select

...

I've had similar issues when working on servers in DMZ or other network restricted "areas", sometimes adding a host record or an cliconfg alias has helped but not always.

😎

Thanks Shiva for an amusing question.

I like the simplicity of the brute force approach although of course it doesn't scale beyond 3 digits. The only criticism I have is...

Quick thought, when the IS is not working, try pressing CTRL+SHIFT+R to refresh the cache, also check the Tools-Options-Text Editor-Transact SQL-IntelliSense memory settings, might want to increase that.

😎

This is interesting, I wonder if we could...

This code returns the node structure of the XML

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @TXML XML = N'<EssRoot>

<T.6.03.0>

<eventTS>2014-09-10T23:51:53.0Z</eventTS>

<equipment unitAddress="105328605" equipType="tractor" alias="0105328605" ID="0105328605" />

<position lat="42.76819444444445" lon="-78.59981481481482" posTS="2014-09-10T23:51:48.0Z" />

<posType>1</posType>

<ignitionStatus>1</ignitionStatus>

<tripStatus>O</tripStatus>

<ltdDistance>0.0</ltdDistance>

<speed>0</speed>

<heading>0</heading>

<workflowEventType>loadAtTerminal</workflowEventType>

<data id="X_XMM_FILTERED">

<datum name="sortId" value="100" />

<datum name="destSortId"...

ScottPletcher (9/16/2014)

---

If there's always at least one decimal place:

SELECT

value AS original_value,

SUBSTRING(value, 1, CHARINDEX('.', value) + 1) + '%' AS new_value

FROM (

...

g_rose42 (9/14/2014)

---

Thanks for the reply. But I think that split function gives the values by row. i need it by column by.

Even though if i convert into row by...

Here is a quick exsample of node shredding based on your XML sample, if the structure of the XML is not changing then this could be the right direction.

😎

USE tempdb;

GO

SET...

Quick solution (if I got this right, only on the first coffee in the morning:-) ), using another Tally table to generate the missing numbers.

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @SerialNumber VARCHAR(1024)...

rob.delong (9/15/2014)

---

Consider a situation where a stored procedure taking a varbinary(max) (BLOB) input parameter then calls a nested stored procedure and passes along that varbinary(max) as an input parameter to...

cstg85 (9/16/2014)

---

I am working on a project to pull data into SQL Server from flat text files. The issue I am having is with the date and time columns. In...