Something like this?

declare @pattern varchar(10)

declare @s varchar(255)

set @pattern='[0-9])'

set @s = 'ABC0)1)22)33))XYZ4)5689'

select count(*)

from numbers

where substring(@s,number,len(@s)) like @pattern+'%'

and number between 1 and len(@s)

see if this helps

declare @string varchar(30)

set @string = '$4.21 plus tax....'

select left(@string,1+len(@string)-patindex('%[^.]%',reverse(@string)))

try this

WITH CTE AS (

SELECT id,name,parent_id, 1 AS Level, CAST(name AS VARCHAR(MAX)) AS FullPath

FROM mytable

WHERE parent_id = 0

UNION ALL

SELECT t.id,t.name,t.parent_id, c.Level+1, CAST(t.name AS VARCHAR(MAX)) +',' + c.FullPath

FROM mytable t

INNER JOIN...

see if this helps

CREATE TABLE tabl1(

EmpId INT,

Emp_Name VARCHAR(20),

ManagerID ...

WITH CTE AS (

SELECT EMPL_ID, effective_dt, ORG,

ROW_NUMBER() OVER(PARTITION BY EMPL_ID ORDER BY effective_dt DESC) AS rn

FROM ORG)

SELECT EMPL_ID, effective_dt, ORG

FROM CTE

WHERE...

@GasSiteId should be @GasSiteID (XML is case-sensitive)

WITH CTE(ColID,ParentColID,ColVal,fullpath) AS (

SELECT ColID,ParentColID,ColVal,CAST(ColVal AS VARCHAR(MAX))

FROM #tmp1

WHERE ParentColID IS NULL

UNION ALL

SELECT p.ColID , p.ParentColID ,p.ColVal, c.fullpath+'/'+CAST(p.ColVal AS VARCHAR(MAX))

FROM CTE c

INNER JOIN #tmp1 p ON p.ParentColID=c.ColID)

SELECT ColID,ParentColID,ColVal

FROM CTE

ORDER BY fullpath

This isn't particularly efficient, but should work okay

WITH LBounds AS(

SELECT s1.ItemCode,s1.Units,s1.Place,s1.Status,s1.StartDate,s1.Flag

FROM mytable s1

WHERE NOT EXISTS(SELECT * FROM mytable s2

...

select substring(@vch,patindex('%[0-9]%',@vch),100)

select ItemNo,

sum(case when Date1 > Date2 then 1 else 0 end),

count(*)

from Table

group by ItemNo

Mahesh Bote (1/16/2009)

---

[font="Verdana"]No, you are right. I have not mentioned the Delete statement.

Delete From Store

Where Store.Metadata.exist('/author[not(contains(., "Per Bothner"))]') >= 0

It should delete those elements whos author is other than...

select object_name(object_id) as tablename, name as columnname,collation_name

from sys.columns

where collation_name is not null

order by object_name(object_id),column_id

Jeff Moden (1/13/2009)

---

Mark,

VERY clever code... I obviously never thought of using PatIndex and a variable for the first character of a range... And, it's about twice as fast as the...

Sergei Ely (1/12/2009)

---

Mark,

Thanks for the input. The solution requires placement of the integer within the ordered string at variable positions. For example, the existing string could be a single...

Here's another way.

SELECT CASE WHEN CHARINDEX(@CharToInsert,@ExistingString) > 0

THEN @ExistingString

...