Concatenate strings in group column

I have following problem:
I want to display a concatenated string in a group row of a report.
The grouping works fine, i can "sum" numerical values etc. as
expected, but i could not find a way to concatenate strings (from the
rows that make up the group). "Join(...)" does not work (gives me an
error message).
I can use "First(...) & Last(...)" to concatenate the string columns
from the first and last row of the group, but couldn't find a way to
concatenate the string columns from all rows...
I am using SQL Server 2005 / Visual Studio 2005.I use a CLR aggregate directly in SQL Server. Hopes your datasource is SQL
Server 2005.
In reports, I use Replace function to change my separator character.
--
Jean-Pierre Riehl
http://blog.djeepy1.net
http://www.bewise.fr
"PR" <pr_gg@.egal.de> wrote in message
news:76e4156a-8805-4e71-927a-7f2c848f295d@.q77g2000hsh.googlegroups.com...
>I have following problem:
> I want to display a concatenated string in a group row of a report.
> The grouping works fine, i can "sum" numerical values etc. as
> expected, but i could not find a way to concatenate strings (from the
> rows that make up the group). "Join(...)" does not work (gives me an
> error message).
> I can use "First(...) & Last(...)" to concatenate the string columns
> from the first and last row of the group, but couldn't find a way to
> concatenate the string columns from all rows...
> I am using SQL Server 2005 / Visual Studio 2005.
>
>|||On Jan 17, 8:21 am, "Jean-Pierre Riehl" <jean-pierre.ri...@.bewise.fr>
wrote:
> I use a CLR aggregate directly in SQL Server. Hopes your datasource is SQL
> Server 2005.
> In reports, I use Replace function to change my separator character.
> --
> Jean-Pierre Riehlhttp://blog.djeepy1.nethttp://www.bewise.fr
> "PR" <pr...@.egal.de> wrote in message
> news:76e4156a-8805-4e71-927a-7f2c848f295d@.q77g2000hsh.googlegroups.com...
>
thanks for the idea. I do use SQL Server 2005, however, since this is
a "group" column in a report, i can't use SQL to concatenate the
strings as i deal with a grouped subset of the sql query result...|||On Jan 16, 9:31=A0am, PR <pr...@.egal.de> wrote:
> I have following problem:
> I want to display a concatenated string in a group row of a report.
> The grouping works fine, i can "sum" numerical values etc. as
> expected, but i could not find a way to concatenate strings (from the
> rows that make up the group). "Join(...)" does not work (gives me an
> error message).
> I can use "First(...) & Last(...)" =A0to concatenate the string columns
> from the first and last row of the group, but couldn't find a way to
> concatenate the string columns from all rows...
> I am using SQL Server 2005 / Visual Studio 2005.
I'm having a hard time visualizing what you want to display. Do you
want your concantenation in each detail row or just in a group header?
Can you mock up an example?|||On Jan 21, 4:06 pm, toolman <t...@.infocision.com> wrote:
> I'm having a hard time visualizing what you want to display. Do you
> want your concantenation in each detail row or just in a group header?
> Can you mock up an example?
I want the concatenation in each detail row. I have some group "sum"s
that work fine, and want to do the same for a string. Basically it's
just a normal grouping of columns with group sums etc.. just that i
need to concatenate a string besides building the group sums.|||On Jan 22, 4:23=A0am, PR <pr...@.egal.de> wrote:
> On Jan 21, 4:06 pm, toolman <t...@.infocision.com> wrote:
> > I'm having a hard time visualizing what you want to display. =A0Do you
> > want your concantenation in each detail row or just in a group header?
> > Can you mock up an example?
> I want the concatenation in each detail row. I have some group "sum"s
> that work fine, and want to do the same for a string. Basically it's
> just a normal grouping of columns with group sums etc.. just that i
> need to concatenate a string besides building the group sums.
Is this what you want?
=3D"String Expression " & SUM(Fields!Name.Value)
It would look something like:
String Expression 1234.56
If you're wanting to concantenate actual field values, say like if
you're grouping on a combination of fields then:
=3DFields!GroupField1.Value & ", " & Fields!GroupField2.Value
would give you something like City, State or Company, Division
HTH|||On Jan 22, 8:57 pm, toolman <t...@.infocision.com> wrote:
> Is this what you want?
> ="String Expression " & SUM(Fields!Name.Value)
> It would look something like:
> String Expression 1234.56
> If you're wanting to concantenate actual field values, say like if
> you're grouping on a combination of fields then:
> =Fields!GroupField1.Value & ", " & Fields!GroupField2.Value
> would give you something like City, State or Company, Division
> HTH
Sorry, no, i don't want to concatenate strings with the sum value of
the group.
I have a grouping in the report, where a sum value (i.e. =Sum(Fields!
Name.NumValue)) is written to one textfield of the row.
What i want to do is concatenate a string from the groups result set -
pretty much like the "Sum" sums up the numerical values from the
result set over the rows. If "Join" would work it would look like
=Join(Fields!Name.StringValue) for the other text field.. but
unfortunately that does not work.
Any ideas ?|||On Jan 23, 3:50=A0am, PR <pr...@.egal.de> wrote:
> On Jan 22, 8:57 pm, toolman <t...@.infocision.com> wrote:
>
> > Is this what you want?
> > =3D"String Expression " & SUM(Fields!Name.Value)
> > It would look something like:
> > String Expression 1234.56
> > If you're wanting to concantenate actual field values, say like if
> > you're grouping on a combination of fields then:
> > =3DFields!GroupField1.Value & ", " & Fields!GroupField2.Value
> > would give you something like City, State or Company, Division
> > HTH
> Sorry, no, i don't want to concatenate strings with the sum value of
> the group.
> I have a grouping in the report, where a sum value (i.e. =3DSum(Fields!
> Name.NumValue)) is written to one textfield of the row.
> What i want to do is concatenate a string from the groups result set -
> pretty much like the "Sum" sums up the numerical values from the
> result set over the rows. If "Join" would work it would look like
> =3DJoin(Fields!Name.StringValue) for the other text field.. but
> unfortunately that does not work.
> Any ideas ?
I think if you can get your Fields!Name.StringValue values into an
array, you could then use Join() to get what you want. Unfortunately,
I'm not enough of a VB or .NET guy to guide you through that.
Hopefully someone else can jump at this point. Sorry I can't get you
further.|||On Jan 23, 5:03 pm, toolman <t...@.infocision.com> wrote:
> I think if you can get your Fields!Name.StringValue values into an
> array, you could then use Join() to get what you want. Unfortunately,
> I'm not enough of a VB or .NET guy to guide you through that.
> Hopefully someone else can jump at this point. Sorry I can't get you
> further.
Thanks for your effort. Yes, Join() requires an array.. and i have no
idea how to convert the string values into an array to do that (and
could not find anything about it in the documentation).|||Hello, PR.
Did you find any solution to your problem? I have the same issue and I can't
find any solution for this...
Thank you.
--
Alexandra
"PR" wrote:
> On Jan 23, 5:03 pm, toolman <t...@.infocision.com> wrote:
> >
> > I think if you can get your Fields!Name.StringValue values into an
> > array, you could then use Join() to get what you want. Unfortunately,
> > I'm not enough of a VB or .NET guy to guide you through that.
> > Hopefully someone else can jump at this point. Sorry I can't get you
> > further.
> Thanks for your effort. Yes, Join() requires an array.. and i have no
> idea how to convert the string values into an array to do that (and
> could not find anything about it in the documentation).
>|||On Feb 22, 12:13 pm, Alexandra Ribeiro
<AlexandraRibe...@.discussions.microsoft.com> wrote:
> Hello, PR.
> Did you find any solution to your problem? I have the same issue and I can't
> find any solution for this...
> Thank you.
> --
> Alexandra
>
Hi Alexandra,
no, i haven't found a solution for this. Maybe using a sub-report is
an option, but i haven't tried this yet.sqlsql

Concatenate String and Pass to FORMSOF?

The following query works perfectly (returning all words on a list called
"Dolch" that do not contain a form of "doing"):
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "doing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
However, what I really want to do requires me to piece two strings together,
resulting in a word like "doing". Any time I try to concatinate strings to
get this parameter, I get an error.
For example:
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "do' + 'ing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
I have also tried using & (as in "do' & 'ing") and various forms of single
and double quotes. Does anyone know a combination that will work?
FYI, in case this query looks goofy because of the unused "CombinedLexicons"
table, it is because the end result should be a working form of the
following...
Figuring out the string concatination is just a step toward this goal:
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
Thanks!
Have you tried using a variable
Something like
DECLARE @.String varchar(100)
SET @.String = 'do' + 'ing'
SET @.String = ' FORMSOF (INFLECTIONAL,"' + @.String + '")'
--PRINT @.String
SELECT ..........................
WHERE CONTAINS(dbo.Dolch.vchWord, @.String)
Andy
"HumanJHawkins" wrote:

> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings together,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused "CombinedLexicons"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>
>
>
|||Would this work for you?
declare @.string varchar(2000)
declare @.searchphrase varchar(200)
set @.searchphrase='do ing'
set @.string='SELECT [Dolch] AS [List Name], dbo.Dolch.vchWord '
select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
select @.string=@.string+ ' dbo.CombinedLexicons ON
CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
select @.string=@.string +replace(@.searchphrase,' ','')
select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
print @.string
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HumanJHawkins" <NoSpam@.NoSpam.Net> wrote in message
news:ZGmJd.5174$r27.4041@.newsread1.news.pas.earthl ink.net...
> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings
together,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused
"CombinedLexicons"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>
>
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OrzwtawAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Would this work for you?
>
> declare @.string varchar(2000)
> declare @.searchphrase varchar(200)
> set @.searchphrase='do ing'
> set @.string='SELECT [Dolch] AS [List Name], dbo.Dolch.vchWord '
> select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
> select @.string=@.string+ ' dbo.CombinedLexicons ON
> CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
> select @.string=@.string +replace(@.searchphrase,' ','')
> select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
> print @.string
It's taking me a while to see if this will work. Thanks for the suggestion.
It looks like a good path to take.

Concatenate String and Pass to FORMSOF?

The following query works perfectly (returning all words on a list called
"Dolch" that do not contain a form of "doing"):
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "doing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
However, what I really want to do requires me to piece two strings together,
resulting in a word like "doing". Any time I try to concatinate strings to
get this parameter, I get an error.
For example:
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "do' + 'ing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
I have also tried using & (as in "do' & 'ing") and various forms of single
and double quotes. Does anyone know a combination that will work?
FYI, in case this query looks goofy because of the unused "CombinedLexicons"
table, it is because the end result should be a working form of the
following...
Figuring out the string concatination is just a step toward this goal:
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
Thanks!Have you tried using a variable
Something like
DECLARE @.String varchar(100)
SET @.String = 'do' + 'ing'
SET @.String = ' FORMSOF (INFLECTIONAL,"' + @.String + '")'
--PRINT @.String
SELECT ..........................
WHERE CONTAINS(dbo.Dolch.vchWord, @.String)
Andy
"HumanJHawkins" wrote:

> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings togethe
r,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused "CombinedLexicon
s"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>
>
>|||Would this work for you?
declare @.string varchar(2000)
declare @.searchphrase varchar(200)
set @.searchphrase='do ing'
set @.string='SELECT [Dolch] AS [List Name], dbo.Dolch.vchWord '
select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
select @.string=@.string+ ' dbo.CombinedLexicons ON
CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
select @.string=@.string +replace(@.searchphrase,' ','')
select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
print @.string
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HumanJHawkins" <NoSpam@.NoSpam.Net> wrote in message
news:ZGmJd.5174$r27.4041@.newsread1.news.pas.earthlink.net...
> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings
together,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused
"CombinedLexicons"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>
>
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OrzwtawAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Would this work for you?
>
> declare @.string varchar(2000)
> declare @.searchphrase varchar(200)
> set @.searchphrase='do ing'
> set @.string='SELECT [Dolch] AS [List Name], dbo.Dolch.vchWord '
> select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
> select @.string=@.string+ ' dbo.CombinedLexicons ON
> CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
> select @.string=@.string +replace(@.searchphrase,' ','')
> select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
> print @.string
It's taking me a while to see if this will work. Thanks for the suggestion.
It looks like a good path to take.sqlsql

Concatenate String and Pass to FORMSOF?

The following query works perfectly (returning all words on a list called
"Dolch" that do not contain a form of "doing"):

SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "doing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)

However, what I really want to do requires me to piece two strings together,
resulting in a word like "doing". Any time I try to concatinate strings to
get this parameter, I get an error.

For example:

SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "do' + 'ing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)

I have also tried using & (as in "do' & 'ing") and various forms of single
and double quotes. Does anyone know a combination that will work?

FYI, in case this query looks goofy because of the unused "CombinedLexicons"
table, it is because the end result should be a working form of the
following...
Figuring out the string concatination is just a step toward this goal:

SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)

Thanks!Would this work for you?

declare @.string varchar(2000)
declare @.searchphrase varchar(200)
set @.searchphrase='do ing'
set @.string='SELECT [Dolch] AS[List Name], dbo.Dolch.vchWord '
select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
select @.string=@.string+ ' dbo.CombinedLexicons ON
CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
select @.string=@.string +replace(@.searchphrase,' ','')
select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
print @.string

--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HumanJHawkins" <NoSpam@.NoSpam.Net> wrote in message
news:ZGmJd.5174$r27.4041@.newsread1.news.pas.earthl ink.net...
> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings
together,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused
"CombinedLexicons"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OrzwtawAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Would this work for you?
>
> declare @.string varchar(2000)
> declare @.searchphrase varchar(200)
> set @.searchphrase='do ing'
> set @.string='SELECT [Dolch] AS[List Name], dbo.Dolch.vchWord '
> select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
> select @.string=@.string+ ' dbo.CombinedLexicons ON
> CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
> select @.string=@.string +replace(@.searchphrase,' ','')
> select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
> print @.string

It's taking me a while to see if this will work. Thanks for the suggestion.
It looks like a good path to take.

Concatenate string

Please help me with this if you can.
I have one table with CustomerID and some other data.
In other table i have CustomerID(the link with the first table) and Agent
The relation of the first with the second one is ONE TO MANY.
I want something like this:
Customer,'Agent1,Agent2,Agent3'

Is it possible.
Please help :)looks like you are trying to reverse pivot...

1) Are there always three agent rows per customer id ?
2) Is there a field which identifies the agent number per customer id?

throw up the ddl to these tables|||First Table
CustomerID|CustomerName

SecondTable
CustomerID|AgentName

Ex Records:

First Table
1| Gigi
2|Vasile

Second Table
1|Ionescu
1|Vasilescu
1|George
2|Marin
2|Preda

I want:
1|'Gigi'|'Ionescu,Vasilescu,George'
2|'Vasile'|'Marin,Preda'|||I did find a solution.
But i'm not sure that is the best one.
I use a function in MS SQL Server and a Cusor inside.
Here it is the function.
Anyway, i know that Cursors are slowly so please give me another solution if you know.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER FUNCTION admin_GetAgentsNames (@.CustomerId int)
RETURNS varchar(1000)
AS

BEGIN
DECLARE @.AgentsNames VARCHAR(1000)
DECLARE @.AgentFName VARCHAR(50)
DECLARE @.AgentLName VARCHAR(50)
DECLARE @.AgentId INT
SET @.AgentsNames = ''
DECLARE curGetAgents CURSOR FOR
SELECT DISTINCT AgentId,ISNULL(c.FNAME,''),ISNULL(c.LNAME,'')
FROM tbl_customerToLocalAgent_Pass AS a
JOIN tbl_Customer AS b ON a.AgentId=b.CustomerId
JOIN tbl_CustomerDetails AS c ON b.CustomerId=c.CustomerId
AND b.PreferredLanguageId=c.LanguageId
WHERE a.CustomerId=@.CustomerId

--Opent the cursor
OPEN curGetAgents
--Fetch the first record
FETCH NEXT FROM curGetAgents INTO @.AgentId,@.AgentFName,@.AgentLName

WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
--Concatenate the names
SET @.AgentsNames = @.AgentsNames + ',' + @.AgentFName + ' ' + @.AgentLName
--Get the next row
FETCH NEXT FROM curGetAgents INTO @.AgentId,@.AgentFName,@.AgentLName
END
--Close cursor
CLOSE curGetAgents
DEALLOCATE curGetAgents

RETURN(RIGHT(@.AgentsNames,LEN(@.AgentsNames)))

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Concatenate Query Results into a String most effectively

Hi all,
I want to concatenate the results from a query in a string in the most
performance-friendly manner. E.g. executing
SELECT Code, Price
FROM Inventory
returns
ABC, 120
EFG, 200
HIJ, 245.50
...
Instead of returning the result in the format above, I want it to look like
this
ABC, 120/ EFG, 200/ HIJ, 245.50/ ...
returned as a string, or a column.
The normal way is to use cursor, and loop all the rows and concatenate the
values in each row into the string. BUT I read cursors are not
performance-friendly.
So what is the most performance-friendly SQL statement(s) that I can write
(preferably in one statement) to do the above?
Please kindly advise. TQ very much in advance.Hi
Why not doung such things on the client side'?
CREATE TABLE #Test
(
col CHAR(1)NOT NULL,
col1 DECIMAL(5,2) NOT NULL
)
INSERT INTO #Test VALUES('A',120)
INSERT INTO #Test VALUES('B',155)
INSERT INTO #Test VALUES('C',20.33)
DECLARE @.st VARCHAR(100)
SET @.st=''
SELECT @.st=@.st+col+','+CAST(col1 AS VARCHAR(10))+'/' FROM #Test
SELECT @.st
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:F18E50B2-F6D0-4FA8-BC2D-2242935F01FE@.microsoft.com...
> Hi all,
> I want to concatenate the results from a query in a string in the most
> performance-friendly manner. E.g. executing
> SELECT Code, Price
> FROM Inventory
> returns
> ABC, 120
> EFG, 200
> HIJ, 245.50
> ...
> Instead of returning the result in the format above, I want it to look
like
> this
> ABC, 120/ EFG, 200/ HIJ, 245.50/ ...
> returned as a string, or a column.
> The normal way is to use cursor, and loop all the rows and concatenate the
> values in each row into the string. BUT I read cursors are not
> performance-friendly.
> So what is the most performance-friendly SQL statement(s) that I can write
> (preferably in one statement) to do the above?
> Please kindly advise. TQ very much in advance.|||Hi,
Thanks. Cool solution. Never think abt that!
"Uri Dimant" wrote:

> Hi
> Why not doung such things on the client side'?
Well I am customizing an existing program... So try to keep my changes
minimum and yet still get good perforamance. ;)
Furthermore doing such things on client side would mean I have to write
additional VB codes and Crystal Report to reformat the whole thing. SQL
statements still so much more powerful.

>
> CREATE TABLE #Test
> (
> col CHAR(1)NOT NULL,
> col1 DECIMAL(5,2) NOT NULL
> )
> INSERT INTO #Test VALUES('A',120)
> INSERT INTO #Test VALUES('B',155)
> INSERT INTO #Test VALUES('C',20.33)
> DECLARE @.st VARCHAR(100)
> SET @.st=''
> SELECT @.st=@.st+col+','+CAST(col1 AS VARCHAR(10))+'/' FROM #Test
> SELECT @.st
>
>
> "HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
> news:F18E50B2-F6D0-4FA8-BC2D-2242935F01FE@.microsoft.com...
> like
>
>

Concatenate columns into a string

I am trying to concatenate 3 columns into 1 string but I need to sort
them alphabetically before I concatenate them.
Example
column1 = ABC
column2 = ABF
column3 = ABE
I need to insert the concatenate value into a column as "ABC ABE ABF"
I know how to concatenate the values but I don't know how the sort them
first.
I would appreciate any advice you can provide.
Thanks,You could try something like this as a brute-force approach:
create table #temp(column1 char(3), column2 char(3), column3 char(3))
insert #temp values('ABC', 'ABF', 'ABE')
insert #temp values('DEF', 'AAF', 'AAF')
select first + ' ' + middle + ' ' + last
from (select
case
when column1 <= column2 and column1 <= column3 then column1
when column2 <= column1 and column2 <= column3 then column2
when column3 <= column1 and column3 <= column2 then column3
end first,
case
when column1 >= column2 and column1 <= column3 then column1
when column1 >= column3 and column1 <= column2 then column1
when column2 >= column1 and column2 <= column3 then column2
when column2 >= column3 and column2 <= column1 then column2
when column3 >= column1 and column3 <= column2 then column3
when column3 >= column2 and column3 <= column1 then column3
end middle,
case
when column1 >= column2 and column1 >= column3 then column1
when column2 >= column1 and column2 >= column3 then column2
when column3 >= column1 and column3 >= column2 then column3
end last
from #temp) x
go
drop table #temp
go|||Have you ever worked in billing at a law firm?
:)
"JeffB" <jeff.bolton@.citigatehudson.com> wrote in message
news:1147465998.777145.245230@.v46g2000cwv.googlegroups.com...
> You could try something like this as a brute-force approach:
> create table #temp(column1 char(3), column2 char(3), column3 char(3))
> insert #temp values('ABC', 'ABF', 'ABE')
> insert #temp values('DEF', 'AAF', 'AAF')
> select first + ' ' + middle + ' ' + last
> from (select
> case
> when column1 <= column2 and column1 <= column3 then column1
> when column2 <= column1 and column2 <= column3 then column2
> when column3 <= column1 and column3 <= column2 then column3
> end first,
> case
> when column1 >= column2 and column1 <= column3 then column1
> when column1 >= column3 and column1 <= column2 then column1
> when column2 >= column1 and column2 <= column3 then column2
> when column2 >= column3 and column2 <= column1 then column2
> when column3 >= column1 and column3 <= column2 then column3
> when column3 >= column2 and column3 <= column1 then column3
> end middle,
> case
> when column1 >= column2 and column1 >= column3 then column1
> when column2 >= column1 and column2 >= column3 then column2
> when column3 >= column1 and column3 >= column2 then column3
> end last
> from #temp) x
> go
> drop table #temp
> go
>|||>> I know how to concatenate the values but I don't know how the sort them
If ordered display of these is significant to your business, perhaps you
should not be representing these values in a single row to begin with.
Please elaborate on your requirements, including some actual sample data,
and someone here can give some advice.
Anith|||(jdornan@.wideopenwest.com) writes:
> I am trying to concatenate 3 columns into 1 string but I need to sort
> them alphabetically before I concatenate them.
> Example
> column1 = ABC
> column2 = ABF
> column3 = ABE
> I need to insert the concatenate value into a column as "ABC ABE ABF"
> I know how to concatenate the values but I don't know how the sort them
> first.
I don't know you intend to concatenate them, but if you are on SQL 2000
you should run a cursor over the data, and the cursor definition should
include an ORDER BY clause. There are tricks that does not use a cursor,
but problem with these tricks is that they rely on undefined behaviour,
and may not yield the desired result.
On SQL 2005, there is another trick - but this time one that relies
on defined behaviour by using FOR XML PATH and XQuery. I show it here
with a sample from the Northwind database:
select CustomerID,
substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
-- strip the last ',' from the list
from
Customers c cross apply
(select convert(nvarchar(30), OrderID) + ',' as [text()]
from Orders o
where o.CustomerID = c.CustomerID
order by o.OrderID
for xml path('')) as Dummy(OrdIdList)
go
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you all for your input. Sorry for the vague post, I will
elaborate some more on what exactly I am doing.
I am running SQL Server 2000.
I currently get data from 4 different sources that I need to combine.
Each source has different parts of the information. For example:
Serial number 111222 for a widget.
Each widget has many different options that are listed as a 3 character
strings. AAA, BBB, CCC
The problem is that I get the options from 4 different sources so I
must combine all the options into 1 long string (separated by a space)
to be used by another application. I can join the tables with the
serial number as the primary key but the kicker is that the options
must be placed in 1 string in alphabetic order for the front end
application to function correctly.
I am using a DTS package to import and concatenate the data right now.
The data is dumped from a mainframe into 4 different txt files
(currently this is the only way we can obtain this data). The order in
which I import the 4 data sources works most of the time.
Unfortunately there are instances where we have option codes that are
not in alphabetic order and the front end app errors out.
I know we can change the front end application to do the sorting but
with 500,000 plus records at a time it would add quite a bit of
overhead to the app. My thought was to combine, sort, and store them
in one field on the server so the front end app doesn't have to do
that processing.|||(jdornan@.wideopenwest.com) writes:
> I am running SQL Server 2000.
> I currently get data from 4 different sources that I need to combine.
> Each source has different parts of the information. For example:
> Serial number 111222 for a widget.
> Each widget has many different options that are listed as a 3 character
> strings. AAA, BBB, CCC
> The problem is that I get the options from 4 different sources so I
> must combine all the options into 1 long string (separated by a space)
> to be used by another application. I can join the tables with the
> serial number as the primary key but the kicker is that the options
> must be placed in 1 string in alphabetic order for the front end
> application to function correctly.
> I am using a DTS package to import and concatenate the data right now.
> The data is dumped from a mainframe into 4 different txt files
> (currently this is the only way we can obtain this data). The order in
> which I import the 4 data sources works most of the time.
> Unfortunately there are instances where we have option codes that are
> not in alphabetic order and the front end app errors out.
> I know we can change the front end application to do the sorting but
> with 500,000 plus records at a time it would add quite a bit of
> overhead to the app. My thought was to combine, sort, and store them
> in one field on the server so the front end app doesn't have to do
> that processing.
That does not sound too fun, at least not on SQL 2000. You could
get the options into a SQL Server table, but you would then have
to run a cursor over the table to build the string for each widget.
I hope that the options are not stored in sources as lists as well,
because that would make things even worse.
Which will perform the worst, doing this in the server or in the
application, I don't know.
If you post CREATE TABLE statements for the tables, and INSERT statements
with sample data, it is possible that someone is able to find a shortcut
of some sort.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsqlsql

concatenate and NULL

I use the command:
exec sp_dboption 'SAP_SAS','concat null yields null','false'
to set the result of a concat (+) to the string and not to null. It works in
the Query Analyzer, but not in the enterprise manager. SQL Server and Agent
are restarted.
Any idea ?
Joia,
You can also SET CONCAT_NULL_YIELDS_NULL ON/OFF which means that each
connection can control its own behavior. EM (& QA, too) issue several SET
commands to make the environment into the one that they want. So, I suspect
that EM is setting this on for you.
If you want to see the list if settings (which I do not remember off the top
of my head) fire up sql profiler to trace TSQL, then connect with EM and see
the list of settings.
The standard OLE DB / ODBC connection also has several settings hard-coded
into it.
Russell Fields
"joia" <joia@.discussions.microsoft.com> wrote in message
news:B732E540-DD1A-45B5-A8FB-A28CE67F1A4D@.microsoft.com...
> I use the command:
> exec sp_dboption 'SAP_SAS','concat null yields null','false'
> to set the result of a concat (+) to the string and not to null. It works
in
> the Query Analyzer, but not in the enterprise manager. SQL Server and
Agent
> are restarted.
> Any idea ?
|||joia,
This is session setting, and is set at runtime using SET
CONCAT_NULL_YIELDS_NULL {ON|OFF}. sp_dboption, 'dbname','concat null
yields null' is only provided as a default if the client connection does
not specify it. Both QA and EM, set this and override the default value
when you connect.
So, if you use QA, it will only work if you go to
Tools->Options->Connection Properties. Then untick Set
concat_null_yields_null. Then, every connection you open (regardless of
the default database setting, this option will be off.
I don't think you can set this in EM, I couldn't find it anyway, so I
might be wrong. By default it seems that EM sets this option ON when you
create a new connection to a server from EM as shown in this Profiler trace:
Audit Login
-- network protocol: LPC
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
joia wrote:
> I use the command:
> exec sp_dboption 'SAP_SAS','concat null yields null','false'
> to set the result of a concat (+) to the string and not to null. It works in
> the Query Analyzer, but not in the enterprise manager. SQL Server and Agent
> are restarted.
> Any idea ?
|||Basically, setting this option through sp_dboption is pretty much worthless,
since it is almost always overridden by the tool when they open a new
connection.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"joia" <joia@.discussions.microsoft.com> wrote in message
news:B732E540-DD1A-45B5-A8FB-A28CE67F1A4D@.microsoft.com...
> I use the command:
> exec sp_dboption 'SAP_SAS','concat null yields null','false'
> to set the result of a concat (+) to the string and not to null. It works
in
> the Query Analyzer, but not in the enterprise manager. SQL Server and
Agent
> are restarted.
> Any idea ?

concatenate a string within a loop from a temp table

I need help.

I have a large table that looks like this.

(ID INT NOT NULL IDENTITY(1,1),PK INT , pocket VARCHAR(10))

1, 1, p1
2, 1, p2
3, 2, p3
4, 2, p4
5, 3, p5
6, 3, p6
7, 4, p7
8, 5, p1
9, 5, p2
10,5, p83

i would like to loop through the table and concatenate the pocket filed for all the records that has the same pk. and insert the pk and the concatenated string into another table in a timely manner.

can anyone help?

Emad

An easy way (but slow if you have a big table) to do this is to use a cursor. Bellow is a fully functional example.

Hope it helps!

DECLARE @.MyTable TABLE (ID INT NOT NULL,PK INT , pocket VARCHAR(10))

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (1, 1, 'p1')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (2, 1, 'p2')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (3, 2, 'p3')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (4, 2, 'p4')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (5, 3, 'p5')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (6, 3, 'p6')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (7, 4, 'p7')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (8, 5, 'p1')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (9, 5, 'p2')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (10,5, 'p83')

DECLARE @.MyResult TABLE (PK Int, SumOfPockets Varchar(4000))

DECLARE @.PK Int

DECLARE @.MyString Varchar(4000)

DECLARE cMyTable CURSOR LOCAL FAST_FORWARD FOR

SELECT PK FROM @.MyTable GROUP BY PK

OPEN cMyTable

FETCH NEXT FROM cMyTable INTO @.PK

WHILE @.@.FETCH_STATUS=0

BEGIN

SET @.MyString = ''

SELECT @.MyString = @.MyString + pocket FROM @.MyTable WHERE PK = @.PK

INSERT INTO @.MyResult(PK, SumOfPockets) VALUES (@.PK, @.MyString)

FETCH NEXT FROM cMyTable INTO @.PK

END

CLOSE cMyTable

DEALLOCATE cMyTable

SELECT * FROM @.MyResult

|||

Doru,

This looks like a great solution although i have a very huge table and i ran out of resources when i tried to implement your solution. I was hopping i can do it with some loops and temp tables. do you have any other ideas.

Emad

|||i CAN'T USE LOCAL VARIABLES BECAUSE THE RESULT OF THE CONCATENATION WOULD BE MORE THAN 8000 CHAR.|||

Emadkb wrote:

i CAN'T USE LOCAL VARIABLES BECAUSE THE RESULT OF THE CONCATENATION WOULD BE MORE THAN 8000 CHAR.

If you're running on SQL Server 2005, you can use VARCHAR(MAX) and NVARCHAR(MAX) variables in your string concatenation.

|||we are still on SQL 2000|||

Hi Emad,

If Varchar(8000) is not big enough then you'll have to use Text datatype in your destination table. This will slow down the whole thing even more :-(.

A solution that should work, but which is very slow, is:

---

DECLARE @.MyTable TABLE (ID INT NOT NULL,PK INT , pocket VARCHAR(10))

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (1, 1, 'p1')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (2, 1, 'p2')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (3, 2, 'p3')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (4, 2, 'p4')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (5, 3, 'p5')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (6, 3, 'p6')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (7, 4, 'p7')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (8, 5, 'p1')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (9, 5, 'p2')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (10,5, 'p83')

CREATE TABLE #MyResult (PK Int, SumOfPockets Text)
DECLARE @.PK Int, @.pocket Varchar(10), @.prev_PK Int
DECLARE @.ptrColText Varbinary(16)

DECLARE cMyTable CURSOR LOCAL FAST_FORWARD FOR
SELECT PK, pocket FROM @.MyTable

OPEN cMyTable
FETCH NEXT FROM cMyTable INTO @.PK, @.pocket

WHILE @.@.FETCH_STATUS=0
BEGIN
IF @.PK = @.prev_PK
UPDATETEXT #MyResult.SumOfPockets @.ptrColText NULL 0 @.pocket
ELSE
BEGIN
INSERT INTO #MyResult(PK, SumOfPockets)
VALUES (@.PK, @.pocket)

SELECT @.ptrColText = TEXTPTR(SumOfPockets)
FROM #MyResult WHERE PK = @.PK

SET @.prev_PK = @.PK
END

FETCH NEXT FROM cMyTable INTO @.PK, @.pocket

END
CLOSE cMyTable
DEALLOCATE cMyTable

SELECT * FROM #MyResult

---

|||

This looks exactly like i want. Excellent work. I will try it and let you know how it goes.

Emad

Concatenate a string problem

Hi to all:

In my package, I have in OLE DB SOURCE a statement:

DECLARE @.CMonth as smalldatetime

SET @.CMonth = '11/1/2006'

select day(@.CMonth)+month(@.CMonth)+year(@.CMonth)as ID_Month

and in OLE DB DESTINATION, I have ID_Month column as a char(10).

I want to be result as concatenated string – 1112006, but I receive 2018 (As a result of calculation)

Can anybody help me? Thank you

DECLARE @.CMonth as smalldatetime

SET @.CMonth = '11/1/2006'

select cast(day(@.CMonth) as varchar(2))+cast(month(@.CMonth) as varchar(2))+cast(year(@.CMonth) as varchar(4)) as ID_Month

|||Thank you very much Attila Soos. It works great!

Concatenate a string in SQL

I have a char(10) column in a table, I would like to concatenate the string
to a variable with coma's in between the strings. I currently use a cursor.
Can any one think of a better way?
--
Thanks,
Jon AHere is a technique:
declare @.myVar varchar(1000)
set @.myVar = ''
select @.myVar = @.myVar + ', ' + myColumn from myTable
set @.myVar = substring(@.myVar,3,1000)
print @.myVar
"Jon A" wrote:

> I have a char(10) column in a table, I would like to concatenate the strin
g
> to a variable with coma's in between the strings. I currently use a cursor
.
> Can any one think of a better way?
> --
> Thanks,
> Jon A|||Here is example from http://vyaskn.tripod.com/code/cat.txt
USE pubs
GO
DECLARE @.title_ids varchar(150), @.delimiter char
SET @.delimiter = ','
SELECT @.title_ids = COALESCE(@.title_ids + @.delimiter, '') + title_id FROM
titles
SELECT @.title_ids AS [List of Title IDs]
Ana
"Jon A" wrote:

> I have a char(10) column in a table, I would like to concatenate the strin
g
> to a variable with coma's in between the strings. I currently use a cursor
.
> Can any one think of a better way?
> --
> Thanks,
> Jon A|||If you mean each column value frpm each row in the the table in a single
comma-delimited string, then no, afaik there is no way without row=based
processing. But a cursor is not the only way... You could use a while loop.
.
Declare @.Out VarChar(8000) Set @.Out = ''
Declare @.ColVal Char(10) Set @.ColVal =''
While exists (Select * From Table
Where ColName > @.ColVal)
Select @.ColVal = Min(ColName),
@.Out = @.Out + RTrim(Min(ColName)) + ','
From Table
Where ColName > @.ColVal
[Untested]
the above approach has the property that it will alphabeticize the entries
in the output tng, and ensure that duplicates are only entered once.
Or, if the Table has a primary key, and you want the duplicates entered
individually,
you can put all the PK values into a table variable and cycle through that
table, delting the records as you use them... This is equivilent to using a
cursor, and much faster...
Declare @.Out VarChar(8000) Set @.Out = ''
Declare @.Val VarChar(10)
Declare @.PK Integer
Declare @.PKs Table (PK Integer Primary Key Not Null)
Insert @.PKs(PK) Select PKColumn From TableName
While exists (Select * From @.PKs) Begin
Select @.PK = Min(PK) From @.PKs
Select @.Out = @.Out + ColName + ','
From Table Wher e PKCol = @.PK
Delete @.PKs Where PK = @.PK
End
hth,
Charly
"Jon A" wrote:

> I have a char(10) column in a table, I would like to concatenate the strin
g
> to a variable with coma's in between the strings. I currently use a cursor
.
> Can any one think of a better way?
> --
> Thanks,
> Jon A|||> I have a char(10) column in a table, I would like to concatenate the string
> to a variable with coma's in between the strings. I currently use a cursor
.
> Can any one think of a better way?
Yes! A better way is to handle presentation and formatting on the client
(that's kind of why it's called the presentation layer).
But of course, like many other inadvisable things, there are kludges to do
this in a query:
http://www.aspfaq.com/2529

Concat rows into string

I have a simple select statement - select groupname FROM Groups
This returns 30 rows. What I want is return everything as comma
seperated string like "group1, group2, group3..."
But I don't want to use function or cursor. Is there anyway? In on
SQL 2000.
thanksPlease have a look at this example:
http://p2p.wrox.com/topic.asp?TOPIC_ID=57982
Cheers,
Paul Ibison

Concat rows into string

I have a simple select statement - select groupname FROM Groups
This returns 30 rows. What I want is return everything as comma
seperated string like "group1, group2, group3..."
But I don't want to use function or cursor. Is there anyway? In on
SQL 2000.
thanksPlease have a look at this example:
http://p2p.wrox.com/topic.asp?TOPIC_ID=57982
Cheers,
Paul Ibison

Concat int with string

Hi,

I am trying to concat an int with an nvarchar in my select:

select distinct(id + name)
from load
where name not like '%test%'

but I am getting an error saying:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the nvarchar value '灪愭楮敭' to a column of data type int.

My question is: how do you concat int and string in MS SQL Server. The equivalent in Oracle would be ||, I think.

Thanks for any help.

Mongodid you try this --

select distinct(cast(id as nvarchar) + name) ...

Computer name could not be obtained

i have stupid problem that had effect my application distripuotion which i think is related either to connection string or something of the security setting of sql server or iis , actually i do not know what to do: the details of the problem is a have multi connection strings in this form :

sqlconnectionPubs =

New Data.SqlClient.SqlConnection("Server=SALES-SERVER;Database=NajdiaServiceRequestDta; Asynchronous Processing=true; Integrated Security=True; Trusted_Connection=Yes;")

and the connection works just fine in my computer , even i put the database in the main server and still i can post data from my pc to the main server where my database exist but when i try to post from any other computer in the network it tells me : run time error : computer name could not be obtained.

I appreciat any help

thanks,

If this is SQLExpress I think you would have to have the connstring be

(SALES-SERVER\SqlExpress Instance name) the default would be

SALES-SERVER\SQLEXPRESS

|||

I still having the same problem that took time more than the time for creating the project and i am afraid that it is related to something in the network that i do not understand. so please keep sending me any new idea.

thanks

|||

w_inaim:

but when i try to post from any other computer in the network it tells me : run time error : computer name could not be obtained.

Where, exactly, are you getting this error? Is it when you try connecting to the database? Are all of your computers on the same domain? Do you have identity impersonate set to true?

sqlsql

Complicated Dynamic Query... a better way?

Just getting started in RS. I've got this dynamic query string:
="select * from v_teds_rpt_workorderlisting where teds_project_nbr=" &
Parameters!teds_project_nbr.Value & iif(Parameters!status.Value="A",
"", " and status='" & Parameters!status.Value &"'") &
iif(Parameters!activity_id.Value=0, "", " and activity_id=" &
Parameters!activity_id.Value) & iif(
IsNumeric(Parameters!w_o_nbr.Value), " and w_o_nbr=" &
Parameters!w_o_nbr.Value, "") &
iif(IsNumeric(Parameters!feas_log_number.Value), " and
feas_log_number=" & Parameters!feas_log_number.Value, "") &
iif(Parameters!component_nbr.Value=0, "", " and component_nbr=" &
Parameters!component_nbr.Value) & iif(Right(Parameters!filter.Value,
2)=":I" and isnumeric(Parameters!filter_on.Value), " and " &
Left(Parameters!filter.Value, Len(Parameters!filter.Value)-2) & "=" &
Parameters!filter_on.Value, "") & iif(Right(Parameters!filter.Value,
2)=":S", " and " & Left(Parameters!filter.Value,
Len(Parameters!filter.Value)-2) & "='" & Parameters!filter_on.Value &
"'", "") & iif(Right(Parameters!filter.Value, 2)=":D" and
IsDate(Parameters!filter_on.Value), " and " &
Left(Parameters!filter.Value, Len(Parameters!filter.Value)-2) & "='" &
Trim(Parameters!filter_on.Value) & "'", "") &" order by " &
Parameters!sort_on.Value & " " & Parameters!sort_order.Value
Nuts, eh? I have to do type checking on a few of the fields because
we have a "Filter On" Drop down which allows the user to pick any
field in the table to filter by. Therefore, I have to typecheck the
text box where they enter the values to make sure a date is a date, a
number is a number, etc.
Would it be better to pass all the parameters into a stored procedure
and build the query there? The script above took a while to make
because it's so easy to misplace a paren or a quote.
Would love to see a shortcut to the Parameters!xxx.Value reference as
well, perhaps P!xxx.valueI haven't fully analyzed your script, however yes a sProc is a viable way to
go...
Further, I suggest either using an sProc that just validates the the types and
then either exec's the working sProc, or returns a helpfull msg, and/or put a
.Net web page as the UI and render the report from your .Net app/aspx page.
HTH
JeffP....
"Larry Bud" <larrybud2002@.yahoo.com> wrote in message
news:5db363e0.0407271034.1dcc4f94@.posting.google.com...
> Just getting started in RS. I've got this dynamic query string:
> ="select * from v_teds_rpt_workorderlisting where teds_project_nbr=" &
> Parameters!teds_project_nbr.Value & iif(Parameters!status.Value="A",
> "", " and status='" & Parameters!status.Value &"'") &
> iif(Parameters!activity_id.Value=0, "", " and activity_id=" &
> Parameters!activity_id.Value) & iif(
> IsNumeric(Parameters!w_o_nbr.Value), " and w_o_nbr=" &
> Parameters!w_o_nbr.Value, "") &
> iif(IsNumeric(Parameters!feas_log_number.Value), " and
> feas_log_number=" & Parameters!feas_log_number.Value, "") &
> iif(Parameters!component_nbr.Value=0, "", " and component_nbr=" &
> Parameters!component_nbr.Value) & iif(Right(Parameters!filter.Value,
> 2)=":I" and isnumeric(Parameters!filter_on.Value), " and " &
> Left(Parameters!filter.Value, Len(Parameters!filter.Value)-2) & "=" &
> Parameters!filter_on.Value, "") & iif(Right(Parameters!filter.Value,
> 2)=":S", " and " & Left(Parameters!filter.Value,
> Len(Parameters!filter.Value)-2) & "='" & Parameters!filter_on.Value &
> "'", "") & iif(Right(Parameters!filter.Value, 2)=":D" and
> IsDate(Parameters!filter_on.Value), " and " &
> Left(Parameters!filter.Value, Len(Parameters!filter.Value)-2) & "='" &
> Trim(Parameters!filter_on.Value) & "'", "") &" order by " &
> Parameters!sort_on.Value & " " & Parameters!sort_order.Value
>
> Nuts, eh? I have to do type checking on a few of the fields because
> we have a "Filter On" Drop down which allows the user to pick any
> field in the table to filter by. Therefore, I have to typecheck the
> text box where they enter the values to make sure a date is a date, a
> number is a number, etc.
> Would it be better to pass all the parameters into a stored procedure
> and build the query there? The script above took a while to make
> because it's so easy to misplace a paren or a quote.
> Would love to see a shortcut to the Parameters!xxx.Value reference as
> well, perhaps P!xxx.value

Complex variable length string combination problem

Hi,
I'm having trouble finding a solution for the following problem:
Suppose you have the following string as input 'sig, ltw, onss'
I would like to generate the following result set:
sig
ltw
onss
sig, ltw
sig, onss
ltw, onns
sig, ltw, onss
This should also work for any number of names (each time seperated by a
comma) within the string ex.:
input = 'sig, ltw, onss, plk'
output =
sig
ltw
onss
plk
sig, ltw
sig, onss
sig, plk
ltw, onns
ltw, plk
onss, plk
sig, ltw, onss
sig, onss, plk
ltw, onss, plk
sig, ltw, onss, plk
Anyone an idea how to resolve this problem?
Thanx in advance...Why don't you put this data in a table rather than a delimited string? TSQL
really isn't a language for string manipulation and delimited data just
shouldn't exist in the database.
The permutations you want to output could be achieved with a self- CROSS
JOIN once you've put the data in a table.
David Portas
SQL Server MVP
--|||You can also do it with the ROLLUP operator:
CREATE TABLE foo (x VARCHAR(5) PRIMARY KEY)
INSERT INTO foo VALUES ('sig')
INSERT INTO foo VALUES ('ltw')
INSERT INTO foo VALUES ('onss')
SELECT A.x, B.x, C.x
FROM foo AS A
JOIN foo AS B
ON B.x <> A.x
JOIN foo AS C
ON C.x <> A.x
AND C.x <> B.x
GROUP BY A.x, B.x, C.x WITH ROLLUP
David Portas
SQL Server MVP
--|||Hi,
I agree that string manipulation is not so easy in T-SQL.
Suppose I put the words as rows in a table; the problem with the crossjoin
is that it returns too many possiblities that are not usefull to me:
given a, b, c
the crossjoin would generate:
a, a, a
a, b, a
a, c, a
b, a, a => already exists but in another order
etc...
As you see whereas in my case I generate 14 possibilities for a 4 names
comma seperated string in your case this will be 27 possibilities; in other
words almost twice as many.
A solution would be to elimate from the crossjoin result the same occurences
of the names but in another order. But how?
"David Portas" wrote:

> Why don't you put this data in a table rather than a delimited string? TSQ
L
> really isn't a language for string manipulation and delimited data just
> shouldn't exist in the database.
> The permutations you want to output could be achieved with a self- CROSS
> JOIN once you've put the data in a table.
> --
> David Portas
> SQL Server MVP
> --|||This is pretty close; if we could just eliminate the "doubles" (in this case
the same names but in a different order) than I would be a happy man... ;-)
"David Portas" wrote:

> You can also do it with the ROLLUP operator:
> CREATE TABLE foo (x VARCHAR(5) PRIMARY KEY)
> INSERT INTO foo VALUES ('sig')
> INSERT INTO foo VALUES ('ltw')
> INSERT INTO foo VALUES ('onss')
> SELECT A.x, B.x, C.x
> FROM foo AS A
> JOIN foo AS B
> ON B.x <> A.x
> JOIN foo AS C
> ON C.x <> A.x
> AND C.x <> B.x
> GROUP BY A.x, B.x, C.x WITH ROLLUP
> --
> David Portas
> SQL Server MVP
> --
>|||Try this one then:
CREATE TABLE foo (x VARCHAR(5) PRIMARY KEY)
INSERT INTO foo VALUES ('sig')
INSERT INTO foo VALUES ('ltw')
INSERT INTO foo VALUES ('onss')
INSERT INTO foo VALUES ('')
SELECT A.x, NULLIF(B.x,''), NULLIF(C.x,'')
FROM foo AS A
LEFT JOIN foo AS B
ON B.x < A.x
LEFT JOIN foo AS C
ON C.x < B.x
WHERE A.x > ''
David Portas
SQL Server MVP
--|||David,
Great!!!! this is exactly what I need.
Is there an easy way to make this code dynamic as to receive any number of
input rows?
This already is great to work on...
thanx man! :-)
"David Portas" wrote:

> Try this one then:
> CREATE TABLE foo (x VARCHAR(5) PRIMARY KEY)
> INSERT INTO foo VALUES ('sig')
> INSERT INTO foo VALUES ('ltw')
> INSERT INTO foo VALUES ('onss')
> INSERT INTO foo VALUES ('')
> SELECT A.x, NULLIF(B.x,''), NULLIF(C.x,'')
> FROM foo AS A
> LEFT JOIN foo AS B
> ON B.x < A.x
> LEFT JOIN foo AS C
> ON C.x < B.x
> WHERE A.x > ''
> --
> David Portas
> SQL Server MVP
> --
>|||The following is really slow, (cause it's resursive), but it works...
First step would be to get the Indiv Strings into a table.. You can use the
following function for that... It's a generally useful function to have
around anyway...
Create Function dbo.ParseString (
@.S VarChar(8000),
@.delim Char(1))
Returns @.tOut Table (ValNum Integer Primary Key Identity, sVal VarChar(500)
)
As
Begin
Declare @.sVal VarChar(80)
Declare @.deLimPos Integer
If right(@.S,1) <> @.Delim Set @.S = @.S + @.Delim
While Len(@.S) > 0
Begin
Set @.deLimPos = CharIndex(@.delim, @.S)
Set @.sVal = Left(@.S,@.deLimPos -1)
Set @.S = Right(@.S,Len(@.S) - @.deLimPos)
Insert @.tOut (sVal) Values (@.sVal)
End
Return
End
-- ----
This function parses a string, using the supplied delimiter, and returns a
table with one row for each entry in the string.
Next, you need a function to "alphabeticize" a delimited string... This one
does the trick...
Create FUNCTION dbo.SortString
(@.S VarChar(1000), @.Delim Char(1))
RETURNS VarChar(1000)
AS
BEGIN
Declare @.Out VarChar(1000) Set @.Out = ''
Declare @.Vals Table (Val VarChar(100))
Declare @.Val VarChar(100)
Insert @.Vals(Val)
Select LTrim(sVal)
From dbo.ParseString(@.S, @.Delim)
While Exists(Select * From @.Vals) Begin
Select @.Val = Min(Val) From @.Vals
Set @.Out = @.Out + @.Val + ','
Delete @.Vals Where Val = @.Val
End
Return Substring(@.Out, 1, Len(@.Out) -1)
END
-- ----
Using these 2 functions, you can dynamically do what you want wit hteh
following:
assuming the variable @.Input contains the original concatenated string...
Declare @.Input VarChar(100) Set @.Input = 'a, b, c'
Declare @.Items Table (ItmNo Integer Primary Key Identity, item varchar(50))
Declare @.Permutations Table(Permutation VarChar(1000))
Insert @.Items (Item)
Select sVal
From dbo.ParseString(@.Input , ',')
Order By sVal
-- ---
--Select * From @.Items
Declare @.NumItems TinyInt
Declare @.Size TinyInt Set @.Size = 0
Select @.NumItems = Count(*) From @.Items
While @.Size <= @.NumItems Begin
Insert @.Permutations (Permutation)
Select Distinct dbo.SortString(IsNull(P.Permutation + ', ', '') +
I.Item, ',')
From @.Items I Left Join @.Permutations P
On I.Item <> IsNull(P.Permutation,'')
Where Not Exists(Select * From @.Permutations
Where Permutation =
dbo.SortString(IsNull(P.Permutation + ', ', '') +
I.Item, ','))
And CharIndex(I.Item, IsNull(P.Permutation,'')) = 0
Set @.Size = @.Size + 1
End
Insert @.Permutations(Permutation) Values('')
Select * From @.Permutations|||Thanks man for your effort.
Will stick to the answer of David Portas found it very elegant.
"CBretana" wrote:

> The following is really slow, (cause it's resursive), but it works...
> First step would be to get the Indiv Strings into a table.. You can use th
e
> following function for that... It's a generally useful function to have
> around anyway...
> Create Function dbo.ParseString (
> @.S VarChar(8000),
> @.delim Char(1))
> Returns @.tOut Table (ValNum Integer Primary Key Identity, sVal VarChar(50
0))
> As
> Begin
> Declare @.sVal VarChar(80)
> Declare @.deLimPos Integer
> If right(@.S,1) <> @.Delim Set @.S = @.S + @.Delim
> While Len(@.S) > 0
> Begin
> Set @.deLimPos = CharIndex(@.delim, @.S)
> Set @.sVal = Left(@.S,@.deLimPos -1)
> Set @.S = Right(@.S,Len(@.S) - @.deLimPos)
> Insert @.tOut (sVal) Values (@.sVal)
> End
> Return
> End
> -- ----
> This function parses a string, using the supplied delimiter, and returns a
> table with one row for each entry in the string.
> Next, you need a function to "alphabeticize" a delimited string... This on
e
> does the trick...
> Create FUNCTION dbo.SortString
> (@.S VarChar(1000), @.Delim Char(1))
> RETURNS VarChar(1000)
> AS
> BEGIN
> Declare @.Out VarChar(1000) Set @.Out = ''
> Declare @.Vals Table (Val VarChar(100))
> Declare @.Val VarChar(100)
> Insert @.Vals(Val)
> Select LTrim(sVal)
> From dbo.ParseString(@.S, @.Delim)
> While Exists(Select * From @.Vals) Begin
> Select @.Val = Min(Val) From @.Vals
> Set @.Out = @.Out + @.Val + ','
> Delete @.Vals Where Val = @.Val
> End
> Return Substring(@.Out, 1, Len(@.Out) -1)
> END
> -- ----
> Using these 2 functions, you can dynamically do what you want wit hteh
> following:
> assuming the variable @.Input contains the original concatenated string...
> Declare @.Input VarChar(100) Set @.Input = 'a, b, c'
> Declare @.Items Table (ItmNo Integer Primary Key Identity, item varchar(50)
)
> Declare @.Permutations Table(Permutation VarChar(1000))
> Insert @.Items (Item)
> Select sVal
> From dbo.ParseString(@.Input , ',')
> Order By sVal
> -- ---
> --Select * From @.Items
> Declare @.NumItems TinyInt
> Declare @.Size TinyInt Set @.Size = 0
> Select @.NumItems = Count(*) From @.Items
>
> While @.Size <= @.NumItems Begin
> Insert @.Permutations (Permutation)
> Select Distinct dbo.SortString(IsNull(P.Permutation + ', ', '') +
> I.Item, ',')
> From @.Items I Left Join @.Permutations P
> On I.Item <> IsNull(P.Permutation,'')
> Where Not Exists(Select * From @.Permutations
> Where Permutation =
> dbo.SortString(IsNull(P.Permutation + ', ', '')
+
> I.Item, ','))
> And CharIndex(I.Item, IsNull(P.Permutation,'')) = 0
> Set @.Size = @.Size + 1
> End
> Insert @.Permutations(Permutation) Values('')
> Select * From @.Permutations
>|||No problemo Peter,
David's solution is much more elegant, and much faster, but as you noticed,
it not dynamic as to the number of items in the imput string...
But as I'm sure you have already figured out, the required processing to
solve this problem increases very very fast as the number of items increases
.
Regards,
Charly
"PeterM" wrote:
> Thanks man for your effort.
> Will stick to the answer of David Portas found it very elegant.
> "CBretana" wrote:
>

Complex variable length combinations of a string

Hi,
Could anyone help me with the following complex problem:
Suppose I have the following input: 'sig, ltw, onss'
Than my output should give the following result set:
sig
ltw
onss
sig, ltw
sig, onss
ltw, onss
sig, ltw, onss
The solution should also work with any given number of names in my comma
seperated string ex.:
input = sig, ltw, onss, pkl
output =
sig
ltw
onss
pkl
sig, ltw
sig, onss
sig, pkl
ltw, onss
ltw, pkl
onss, pkl
sig, ltw, onss
sig, onss, pkl
ltw, onss, pkl
sig, ltw, onss, pkl
Anyone an idea how to resolve this with T-SQL?
Thanx...Peter,
Took me some time to figure this out:
set nocount on
declare @.str as varchar(8000)
,@.count as int
,@.out as varchar(8000)
create table #str
(i int identity(0, 1)
, el varchar(4000))
set @.str='sig, ltw, onss'
--extract elements into a table
set @.str = @.str + ','
while @.str <> '' begin
insert into #str (el)
values (substring(@.str, 1, charindex(',', @.str) - 1))
set @.str = ltrim(substring(@.str, charindex(',', @.str) + 1, 8000))
end
--calculate number of combinations
select @.count = power(2, count(*)) - 1 from #str
--create all combinations
--each element as a binary position where
--1 means element is in and 0 means element is not in a given combination
while @.count > 0 begin
set @.out = ''
select @.out = @.out + el + ', '
from #str
where @.count & power(2, i) = power(2, i)
order by i
select left(@.out, len(@.out) - 1)
set @.count = @.count - 1
end
drop table #str
Ilya
"PeterM" <PeterM@.discussions.microsoft.com> wrote in message
news:A2E5AF3D-4AEF-4E79-9395-639513E06D4E@.microsoft.com...
> Hi,
> Could anyone help me with the following complex problem:
> Suppose I have the following input: 'sig, ltw, onss'
> Than my output should give the following result set:
> sig
> ltw
> onss
> sig, ltw
> sig, onss
> ltw, onss
> sig, ltw, onss
> The solution should also work with any given number of names in my comma
> seperated string ex.:
> input = sig, ltw, onss, pkl
> output =
> sig
> ltw
> onss
> pkl
> sig, ltw
> sig, onss
> sig, pkl
> ltw, onss
> ltw, pkl
> onss, pkl
> sig, ltw, onss
> sig, onss, pkl
> ltw, onss, pkl
> sig, ltw, onss, pkl
> Anyone an idea how to resolve this with T-SQL?
> Thanx...
>|||Ilya, Thanks for your effort in resolving this; but look at the post just
before (Oops I sended it twice) the answer of David Portas is such a elegant
solution, you have to see it to belief it :-)
Thanx
"Ilya Margolin" wrote:

> Peter,
> Took me some time to figure this out:
> set nocount on
> declare @.str as varchar(8000)
> ,@.count as int
> ,@.out as varchar(8000)
> create table #str
> (i int identity(0, 1)
> , el varchar(4000))
> set @.str='sig, ltw, onss'
> --extract elements into a table
> set @.str = @.str + ','
> while @.str <> '' begin
> insert into #str (el)
> values (substring(@.str, 1, charindex(',', @.str) - 1))
> set @.str = ltrim(substring(@.str, charindex(',', @.str) + 1, 8000))
> end
> --calculate number of combinations
> select @.count = power(2, count(*)) - 1 from #str
> --create all combinations
> --each element as a binary position where
> --1 means element is in and 0 means element is not in a given combination
> while @.count > 0 begin
> set @.out = ''
> select @.out = @.out + el + ', '
> from #str
> where @.count & power(2, i) = power(2, i)
> order by i
> select left(@.out, len(@.out) - 1)
> set @.count = @.count - 1
> end
> drop table #str
> Ilya
>
> "PeterM" <PeterM@.discussions.microsoft.com> wrote in message
> news:A2E5AF3D-4AEF-4E79-9395-639513E06D4E@.microsoft.com...
>
>|||Peter,
That was my first thought. The downside of it you have to modify the
statement to add more joins as you add combination members, so the solution
is not scalable.
Ilya
"PeterM" <PeterM@.discussions.microsoft.com> wrote in message
news:D5230490-26F4-4E72-915B-53688DFFC618@.microsoft.com...
> Ilya, Thanks for your effort in resolving this; but look at the post just
> before (Oops I sended it twice) the answer of David Portas is such a
elegant
> solution, you have to see it to belief it :-)
> Thanx
> "Ilya Margolin" wrote:
>
combination
comma|||You're right !
I was wondering how big the difference in performance is between the two
solutions? Have to try it out...
Thanx again :-)
"Ilya Margolin" wrote:

> Peter,
> That was my first thought. The downside of it you have to modify the
> statement to add more joins as you add combination members, so the solutio
n
> is not scalable.
> Ilya
> "PeterM" <PeterM@.discussions.microsoft.com> wrote in message
> news:D5230490-26F4-4E72-915B-53688DFFC618@.microsoft.com...
> elegant
> combination
> comma
>
>|||Ilya,
I had to write you back to say that your solution is by far the most elegant
of all.
I had to take some time to understand your solution but once I understood
it; I found it just brilliant.
Sorry for this late appreciation of your solution ;-)
Thanx again,
Peter
"Ilya Margolin" wrote:

> Peter,
> That was my first thought. The downside of it you have to modify the
> statement to add more joins as you add combination members, so the solutio
n
> is not scalable.
> Ilya
> "PeterM" <PeterM@.discussions.microsoft.com> wrote in message
> news:D5230490-26F4-4E72-915B-53688DFFC618@.microsoft.com...
> elegant
> combination
> comma
>
>

Comparing two strings inside a stored procedure

Basically I have two strings. Both strings will contain similar data because the 2nd string is the first string after an update of the first string takes place. Both strings are returned in my Stored Procedure

For example:
String1 = "Here is some data. lets type some more data"
String2 = "Here's some data. Lets type some data here"

I would want to change string2 (inside my Stored Procedure) to show the changed/added text highlighted and the deleted text with a strike though.

So I would want string2 to look like this

string2 = "Here<font color = \"#00FF00\">'s</font> <strike>is</strike> some data. <font color = \"#00FF00\">L</font>ets type some <strike>more</strike> data <font color = \"#00FF00\">here</font>"

Is there an way to accomplish this inside a stored procedure?

First, you'll have to decide what algorithm determines what matches and what is different. For example, if you just compare the strings by the same position, you'll get the first part the same, the end of the first string as deleted and the end of the second string as inserted. I don't know how to implement the algorithm but I can think of some possibilities:

1. Regular expressions may have some support for this.

2. Track the user's changes character by character on the client.

3. Find a differencing algorithm / tool somewhere.

Once you've solved that, I suggest you pass the difference string to your procedure. You'll have much more flexibility and support in .NET than in SQL.

Sorry I couldn't help more. Good Luck.

|||Yes, you can. However, that is best left up to an application as it's a matter of presentation and application logic.

comparing two string columns and show difference

Hello,

I am having this problem. I have a table to log any changes to the configuration of a program. If something changes, it gets recorded in this table.

So, I have two columns: BeforeChange, AfterChange. It looks like this:

BeforeChange AfterChange
------------------------
Amount = 30.00, Quantity = 3 Amount=30.00, Quantity=2

When I query those changes, I want to show only the changed values.
So, it will show only:

ChangeMade
------
Quantity = 2

Can anyone help me on this?

Thanks in advance.What about to normalize your table - problem will be resolved by itself.|||Originally posted by snail
What about to normalize your table - problem will be resolved by itself.

The problem is that, this table is filled by a program that I have no access to. (Honestly, I don't really know about the program at all)

I can only query the modificationlog table, and I have to make a report of any changes made with stored procedure.

If you can enlighten me a bit more, I would really appreciate it. I'm new to this MS SQL)

Thank you...