I receive this data in a column in a table: $8982162:2$905122:2$905PPA:
25$9601217:2$962B19:2$CSRI96:2$WDCOLACK:2$IDRBLACK 2:2$IDRRED:2$

I need to make a view that will keep only the entries in that string
that start with 'WDCO' and omit the rest

So I need to edit text in the process of copying the data from the
table to the view. Hope this makes sense, sorry, I'm a newb.

SQL 2008 with patches.

Johnnyb wrote:
Please show us exactly what you want to extract from that string in addition
to attempting to describe/explain it. Based on your description, you want
the query/view to return:
WDCOLACK:2$IDRBLACK2:2$IDRRED:2$

Is that your intent?
Or do the $ symbols represent delimiters for "entries" in the string, in
which case this is what you want to return:
WDCOLACK:2

On Feb 10, 1:16*pm, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:
Sorry, it is the second answer. I get random entries - like
$8982162:2$905122:2$905PPA:25$9601217:2$962B19:2$C SRI96:2$WDCOLACK:
2$IDRBLACK2:2$IDRRED:2$. The $'s are delimiters, and I only want to
capture the entires that start with 'WDCO'.

Thanks

Johnnyb wrote:
those handled? Again, show us, don't just describe it.

On Feb 11, 7:03*am, "Bob Barrows" <reb01... (AT) NOSPAMyahoo (DOT) com> wrote:
There will never be multiple occurrences in the same string.

This is how it works:

There are ~1100 possible entires ('$' delimited) that will show up in
the original string. There are no duplicates.

$8982162:2$905122:2$905PPA:25$9601217:2$962B19:2$C SRI96:2$WDCOLACK:
2$IDRBLACK2:2$IDRRED:2$

This is an example of what might come into the original table. I need
to filter this list of entries so that the view have only the ones
that start with WDCO. So no matter how many '$' delimited entries show
up in the original table, I only want to see the WDCO ones in the view.

Johnnyb wrote:
First create a Numbers table using the code found here:
http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum

Then this code will work:
SELECT
'$8982162:2$905122:2$905PPA:25$9601217:2$962B19:2$ CSRI96:2$WDCOLACK:2$IDRBLA
CK2:2$IDRRED:2$' Delimited_string
INTO #t
UNION all
SELECT '$CSRI96:2$WDCOLACK:3$IDRBLACK2:2$IDRRED:2$'


SELECT (
SELECT Value FROM (SELECT Value =
substring(Delimited_string, Number,
charindex('$', Delimited_string + '$', Number) -
Number)
FROM Numbers
WHERE Number <= len(Delimited_string)
AND substring('$' + Delimited_string, Number, 1) = '$') q
WHERE Value LIKE 'WDCO%' ) WDCO_string
FROM #t AS t

DROP TABLE #t

I've avoided the udf solution because of the performance problems inherent
in such functions.

Johnnyb (john.lutheran (AT) gmail (DOT) com) writes:
Use a list-to-table function to convert the entries to table, then you
can use a plain LIKE to find the rows that interest you.

You find several such functions on
http://www.sommarskog.se/arrays-in-sql-2005.html


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On 2011-02-11 16:10, Johnnyb wrote:
Not sure I fully understould the problem, but ...

with t (s) as (
select
'$8982162:2$905122:2$905PPA:25$9601217:2$962B19:2$ CSRI96:2$WDCOLACK:2$IDRBLACK2:2$IDRRED:2$'
), t2 (s, start_pos) as (
select s, CHARINDEX('$WDCO', s) as start_pos from t
)
select
SUBSTRING(s,start_pos, CHARINDEX('$', s, start_pos+1) - start_pos)
from t2;

$WDCOLACK:2

/Lennart