Syntax Help - 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 2005 Forums

Transact-SQL (2005)

Syntax Help

Author

Topic

vprangeddsi
Starting Member

1 Post

Posted - 2009-04-02 : 15:52:50

I have a field that can have data that looks like the following:

BP_12345
BP_12345_1
BP_12345_100
BP_1234566_1
BP_123

I would like to write a query that returns the data between the 2 underscore characters.

Can anybody provide me a quick and simple solution?

TIA

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-02 : 16:39:16

[code]CREATE FUNCTION ParseValues
(@String varchar(8000)
)
RETURNS @RESULTS TABLE
(ID int identity(1,1),
Val varchar(100)
)
AS
BEGIN
DECLARE @Value varchar(100)

WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN CHARINDEX('_',@String) >0 THEN LEFT(@String,CHARINDEX('_',@String)-1) ELSE @String END,
@String=CASE WHEN CHARINDEX('_',@String) >0 THEN SUBSTRING(@String,CHARINDEX('_',@String)+1,LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END[/code]

[code]select
a,
val
from
(
select 'BP_12345' a union all
select 'BP_12345_1' union all
select 'BP_12345_100' union all
select 'BP_1234566_1' union all
select 'BP_123'
)s
cross apply ParseValues(s.a) AS O
where
id=2[/code]

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-04-07 : 05:12:52

Hi,
try this

declare @Customer table
(cid int,
item varchar(50))
insert into @Customer values (1,'BP_12345')
insert into @Customer values (1,'BP_12345_1')
insert into @Customer values(1,'BP_12345_100')
insert into @Customer values(1,'BP_1234566_1')
insert into @Customer values(2,'BP_123')
insert into @Customer values(2,'123')
insert into @Customer values(2,'_BP_123')

select substring(temp,0,case charindex('_',temp) when 0 then len(temp)+1 else charindex('_',temp) end ),charindex('_',temp)
,temp from
(select substring(item,case charindex('_',item) when 0 then 0 else charindex('_',item)+1 end ,len(item)+1 )
as temp from @Customer) a

kunal

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 05:21:42

[code]DECLARE @Sample TABLE
(
Data VARCHAR(100)
)

INSERT @Sample
SELECT 'BP_12345' UNION ALL
SELECT 'BP_12345_1' UNION ALL
SELECT 'BP_12345_100' UNION ALL
SELECT 'BP_1234566_1' UNION ALL
SELECT 'BP_123'

-- Peso 1
SELECT Data,
PARSENAME(REPLACE(CASE
WHEN Data LIKE '%[_]%[_]%' THEN Data
ELSE Data + '_z'
END, '_', '.'), 2)
FROM @Sample

-- Peso 2
SELECT Data,
CASE CHARINDEX('_', SUBSTRING(Data, 4, LEN(Data)))
WHEN 0 THEN SUBSTRING(Data, 4, LEN(Data))
ELSE SUBSTRING(Data, 4, CHARINDEX('_', SUBSTRING(Data, 4, LEN(Data))) - 1)
END
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources