Need help with s a substring - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help with s a substring

Author

Topic

namasteall2000
Starting Member

20 Posts

Posted - 2011-05-04 : 14:06:08

I have a column in my database which has values as "YYNYYNYY" OR "YYYYYN" OR "YYYYYYY" OR "NNNNN"

What I need to do is to check the location of "N" and based on the location of "N" pick the corresponding line.

e.g "YYNYYNYY" should give me location as "3,6" and then I need to pick data of line 3 and 6 from another table.

I tried to do substring with patindex but doesn't give me the location where N exists -

Select substring('YYNYYNYY', patindex('%N%, 'YYNYYNYY'),1)

Please help.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-04 : 14:27:38

Proof of concept, may require changes based on your feedback:

;WITH n(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n<20),
	yn(yn) AS (SELECT 'YYNYYNYY' UNION ALL SELECT 'YYYYYN' UNION ALL SELECT 'YYYYYYY' UNION ALL SELECT 'NNNNN'),
	lines(line, DATA) AS (SELECT n, 'Line ' + CAST(n AS VARCHAR) FROM n)  -- just an example
SELECT yn, DATA, n 
FROM yn
CROSS JOIN n 
INNER JOIN lines l ON n.n=l.line 
WHERE SUBSTRING(yn,n,1)='N'
ORDER BY yn, n

namasteall2000
Starting Member

20 Posts

Posted - 2011-05-04 : 15:23:19

Thanks Robvolk. Sorry -I couldn't understand exactly what you are doing .

Let me make you understand what I want. This is a dummy table I created - I need to update the location like the way I am doing in the first row (I put the location to make you understand the desired results). I know the way using T-SQL - using loops etc. But looking for a solution to get it done in the SQL, if possible.
`
Create table POA ( Indicator varchar(30), location varchar(30))

insert into POA values ('YYNN', '3,4')
insert into POA values ('YYYYN', '')
insert into POA values ('YNNY', '')
insert into POA values ('YYYYYYN', '')
insert into POA values ('NYYYYY', '')

Select * from POA

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-04 : 16:01:41

Afraid this one is more complicated:

WITH P(Ind, loc) AS (SELECT Indicator, location FROM POA WHERE location=''),
	n(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n<20),
	u(Ind,Loc) AS (
SELECT Ind, STUFF((SELECT ',' + CAST(n AS VARCHAR) FROM n 
	CROSS JOIN P WHERE P.Ind=A.Ind AND SUBSTRING(a.ind,n,1)='N' 
	ORDER BY n FOR XML PATH('')),1,1,'')
FROM p a)
UPDATE P SET loc=u.loc
FROM P INNER JOIN u ON p.Ind=u.Ind

The problem with this is that if the indicator changes you'll have to re-run the UPDATE statement.

namasteall2000
Starting Member

20 Posts

Posted - 2011-05-05 : 11:49:39

Thanks Robvolk - will try it soon.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources