Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1576

Re: Need help creating a view that can edit a string.

Show all headers | View raw

Johnnyb wrote:
> On Feb 11, 7:03 am, "Bob Barrows" <reb01...@NOSPAMyahoo.com> wrote:
>> Johnnyb wrote:
>>> On Feb 10, 1:16 pm, "Bob Barrows" <reb01...@NOyahooSPAM.com> wrote:
>>>> Johnnyb wrote:
>>>>> I receive this data in a column in a table:
>>>>> $8982162:2$905122:2$905PPA:
>>>>> 25$9601217:2$962B19:2$CSRI96:2$WDCOLACK:2$IDRBLACK2: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.
>>
>>>> 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
>>

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.

Back to comp.databases.ms-sqlserver | Previous | Next | Find similar

Thread

Re: Need help creating a view that can edit a string. "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2011-02-11 15:23 -0500

csiph-web