asked on

MS SQL Query

Hi,

I have a field that contains data like this:

1 4965 ^^<>1952</>
1 4965 ^
1 2365 ^^<>1952</>
1 5422 ^
1 5433 ^^<>1800</>

There is space before 1 as well.

I would like to run a query to extract the value between <></> and when I don't have <></> to just return ''. So the result should be

1952

1952

1800

Can you please help me with the query?

Thanks.

Anthony Perkins

Something like this perhaps:

SELECT CASE 
		WHEN CHARINDEX('<>', Field) > 0 THEN
			CASE 
				WHEN CHARINDEX('</>', Field) > 0 THEN
					CASE 
						WHEN CHARINDEX('<>', Field) < CHARINDEX('</>', Field) THEN SUBSTRING(Field, CHARINDEX('<>', Field) + 2, CHARINDEX('</>', Field) - CHARINDEX('<>', Field) - 2)
						ELSE ''
					END
				ELSE ''
			END
		ELSE ''
	END
FROM	MyTable

Open in new window

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

|             FIELDX | LFT | RGT | COLUMN_3 |
|--------------------|-----|-----|----------|
| 1 4965 ^^<>1952</> |   8 |  16 |     1952 |
|           1 4965 ^ |   0 |   0 |   (null) |
| 1 2365 ^^<>1952</> |   8 |  16 |     1952 |
|           1 5422 ^ |   0 |   0 |   (null) |
| 1 5433 ^^<>1800</> |   8 |  16 |     1800 |

Open in new window

select
        fieldx
      , ca.lft
      , ca.rgt
      , case when ca.lft > 4 and ca.rgt > 3 then substring(fieldx,ca.lft+4,(ca.rgt-ca.lft)-4) else NULL end
from table1
cross apply (
            select patindex('%^^<>%',fieldx), patindex('%</>%',fieldx)
             ) ca (lft, rgt)

Open in new window

CREATE TABLE Table1
	([fieldx] varchar(40))
;
	
INSERT INTO Table1
	([fieldx])
VALUES
	('1 4965 ^^<>1952</>'),
	('1 4965 ^'),
	('1 2365 ^^<>1952</>'),
	('1 5422 ^'),
	('1 5433 ^^<>1800</>')
;

Open in new window

http://sqlfiddle.com/#!3/2897a/1

ezzadin

ASKER

Worked. Great. Thanks.

ezzadin

ASKER

@PortletPaul, sorry, I saw your solution after accepting Anthony's.

PortletPaul

yes, an 18 second gap between those events :)

no matter. I like using cross apply for these sorts of queries as the aliases (such as "lft") can then be used in the select clause making it a bit easier to understand (in my view)