Pease help - Feild value is truncated

I use a stored procedure to populate a table and it truncates the value to
one character for the field eMail.
you have an idea what is the problem?
i have to say also that I have tried this and it works:
INSERT VH_Data_NewsEmail (Id, eMail) VALUES (@.IdMaxPlus, `string`)
So it looks like there is a problem at the @.eMailPlus variable level.
TABLE IS:
Id int
eMail varchar(MAX)
STORED PROCEDURE IS:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_myInsertStoredProcedure] @.eMail varchar AS
DECLARE @.IdMaxPlus AS INT
DECLARE @.eMailPlus AS VARCHAR
BEGIN TRANSACTION
SET @.eMailPlus = 'Ring' VARCHAR
SELECT @.IdMaxPlus = coalesce(MAX(Id), 0) + 1 FROM VH_Data_NewsEmail
(UPDLOCK)
INSERT VH_Data_NewsEmail (Id, eMail) VALUES (@.IdMaxPlus, @.eMailPlus)
COMMIT TRANSACTIONok fixed it
DECLARE @.eMailPlus AS VARCHAR(MAX)
"Progman" <adfawefqw@.hotmail.com> wrote in message
news:1fmKf.2089$Jb7.681075@.weber.videotron.net...
>I use a stored procedure to populate a table and it truncates the value to
>one character for the field eMail.
> you have an idea what is the problem?
> i have to say also that I have tried this and it works:
> INSERT VH_Data_NewsEmail (Id, eMail) VALUES (@.IdMaxPlus, `string`)
> So it looks like there is a problem at the @.eMailPlus variable level.
> TABLE IS:
> Id int
> eMail varchar(MAX)
> STORED PROCEDURE IS:
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> CREATE PROCEDURE [dbo].[sp_myInsertStoredProcedure] @.eMail varchar AS
> DECLARE @.IdMaxPlus AS INT
> DECLARE @.eMailPlus AS VARCHAR
> BEGIN TRANSACTION
> SET @.eMailPlus = 'Ring' VARCHAR
> SELECT @.IdMaxPlus = coalesce(MAX(Id), 0) + 1 FROM VH_Data_NewsEmail
> (UPDLOCK)
> INSERT VH_Data_NewsEmail (Id, eMail) VALUES (@.IdMaxPlus, @.eMailPlus)
> COMMIT TRANSACTION
>

PDF export can't handle special characters?

I have a field on my report which needs to show an amount in Euros, so I have the Euro (â?¬) symbol in the report. This looks fine when I render the report in HTML and Excel, but when I render in PDF the text is all messed up. This also happens for some other characters with an ASCII code above 127...
Is this a known problem and how can we solve it?
Thanks,
VincentRelated posts:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=a199e6c6-c2e4-40d6-a89d-3f991a02cf3e
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=be0ab01e-7550-42c1-9617-13f3fb442cef
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Vincent" <Vincent@.discussions.microsoft.com> wrote in message
news:9D40C734-F1A8-47CA-809E-2008EFD6E338@.microsoft.com...
> I have a field on my report which needs to show an amount in Euros, so I
have the Euro (â?¬) symbol in the report. This looks fine when I render the
report in HTML and Excel, but when I render in PDF the text is all messed
up. This also happens for some other characters with an ASCII code above
127...
> Is this a known problem and how can we solve it?
> Thanks,
> Vincent
>|||Thanks for the related posts. This is indeed related to the version of the Acrobat Reader (works as of version 6.0), but I have also found a workaround for people who can only use Acrobat Reader 5.0:
Avoid the usage of Arial, Courier and Times New Roman as font type, since these are the fonts that can not be displayed properly with the euro symbol. Alternative could be Arial Unicode MS, Garamond, Microsoft Sans Serif or Verdana, these all look OK when I tested this.
I will also update the other posts, since this is interesting stuff!
"Ravi Mumulla (Microsoft)" wrote:
> Related posts:
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=a199e6c6-c2e4-40d6-a89d-3f991a02cf3e
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=be0ab01e-7550-42c1-9617-13f3fb442cef
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Vincent" <Vincent@.discussions.microsoft.com> wrote in message
> news:9D40C734-F1A8-47CA-809E-2008EFD6E338@.microsoft.com...
> > I have a field on my report which needs to show an amount in Euros, so I
> have the Euro (ââ'¬) symbol in the report. This looks fine when I render the
> report in HTML and Excel, but when I render in PDF the text is all messed
> up. This also happens for some other characters with an ASCII code above
> 127...
> >
> > Is this a known problem and how can we solve it?
> >
> > Thanks,
> > Vincent
> >
>
>|||I am having the same problem in displaying Greek. Works fine on acrobat 6
and I am using Courier New. My problem is that I can not avoid Courier
because I need the font to be fixed font instead of True Type (I am
displaying text that need to be aligned). Is there another Fixed Type font
that will work with Greek (or special characters) for Acrobat 5?
"Vincent" wrote:
> Thanks for the related posts. This is indeed related to the version of the Acrobat Reader (works as of version 6.0), but I have also found a workaround for people who can only use Acrobat Reader 5.0:
> Avoid the usage of Arial, Courier and Times New Roman as font type, since these are the fonts that can not be displayed properly with the euro symbol. Alternative could be Arial Unicode MS, Garamond, Microsoft Sans Serif or Verdana, these all look OK when I tested this.
> I will also update the other posts, since this is interesting stuff!
> "Ravi Mumulla (Microsoft)" wrote:
> > Related posts:
> > http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=a199e6c6-c2e4-40d6-a89d-3f991a02cf3e
> > http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=be0ab01e-7550-42c1-9617-13f3fb442cef
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> > "Vincent" <Vincent@.discussions.microsoft.com> wrote in message
> > news:9D40C734-F1A8-47CA-809E-2008EFD6E338@.microsoft.com...
> > > I have a field on my report which needs to show an amount in Euros, so I
> > have the Euro (ââ'¬) symbol in the report. This looks fine when I render the
> > report in HTML and Excel, but when I render in PDF the text is all messed
> > up. This also happens for some other characters with an ASCII code above
> > 127...
> > >
> > > Is this a known problem and how can we solve it?
> > >
> > > Thanks,
> > > Vincent
> > >
> >
> >
> >

PDF export

I have a main report with 2 subreports as the only items
in a list control. I am linking the 2 reports to a field
in the dataset of the main report. When I view the
report from the report manager or just from the report
URL it looks fine. When I export to PDF the first page
is blank. The 2nd page and all other pages are fine, but
the data from the first page just isn't there. The data
on the 2nd page is supposed to be the 2nd page so it's
just the first page that is not showing up. Has anyone
else seen this and have an idea of how to correct it?Rick,
I see this same problem with my report. Although with mine I have about 20
pages, but if I re-order those pages moving any other page to the first page
position, that page becomes blank, and the old first page is fine, so no
matter what is on the page, it is always the first one that is blank. I have
posted around on this and have not gotten a working response, I did however
find a MSDN "chat" where microsoft briefly mentioned this and said they knew
about it but did not know how to fix it
(http://www.microsoft.com/technet/community/chats/trans/sql/sql060704.mspx)
well post here if you ever get a solution. Thanks and sorry I couldnt be more
helpful.
David
"Rick" wrote:
> I have a main report with 2 subreports as the only items
> in a list control. I am linking the 2 reports to a field
> in the dataset of the main report. When I view the
> report from the report manager or just from the report
> URL it looks fine. When I export to PDF the first page
> is blank. The 2nd page and all other pages are fine, but
> the data from the first page just isn't there. The data
> on the 2nd page is supposed to be the 2nd page so it's
> just the first page that is not showing up. Has anyone
> else seen this and have an idea of how to correct it?
>

PDF and new line

Here is a very funny problem and I searched, tried every method and cannot
figure this out.
Problem: If my field has carriage returns at the start and then some text
and then again carriage return, the HTML shows fine but when exported to PDF
start Carriage returns are not displayed.
Example:
Database value:
3 carriage return and this is text.
2 Carriege return and this is final text
HTML shows:
3 carriage return and this is text.
2 Carriege return and this is final text
PDF shows:
3 carriage return and this is text.
2 Carriege return and this is final text
NO INITAL carriage return in PDF but it does leave once there is some text.
Is this the rendering problem or a bug'
Note: I am not using RTRIM or TRIM anywhere.please help....
I need this resolved for today's build.
thanks,
Rahul
"RK" wrote:
> Here is a very funny problem and I searched, tried every method and cannot
> figure this out.
> Problem: If my field has carriage returns at the start and then some text
> and then again carriage return, the HTML shows fine but when exported to PDF
> start Carriage returns are not displayed.
> Example:
> Database value:
>
> 3 carriage return and this is text.
> 2 Carriege return and this is final text
>
> HTML shows:
>
> 3 carriage return and this is text.
> 2 Carriege return and this is final text
>
> PDF shows:
> 3 carriage return and this is text.
> 2 Carriege return and this is final text
>
> NO INITAL carriage return in PDF but it does leave once there is some text.
> Is this the rendering problem or a bug'
> Note: I am not using RTRIM or TRIM anywhere.|||I found it.
Replace(string, CHAR(13), CHAR(0)
PDF considers char(0), NULL ad adds a carriage return.
"RK" wrote:
> please help....
> I need this resolved for today's build.
> thanks,
> Rahul
> "RK" wrote:
> > Here is a very funny problem and I searched, tried every method and cannot
> > figure this out.
> >
> > Problem: If my field has carriage returns at the start and then some text
> > and then again carriage return, the HTML shows fine but when exported to PDF
> > start Carriage returns are not displayed.
> >
> > Example:
> >
> > Database value:
> >
> >
> >
> > 3 carriage return and this is text.
> >
> > 2 Carriege return and this is final text
> >
> >
> >
> > HTML shows:
> >
> >
> >
> > 3 carriage return and this is text.
> >
> > 2 Carriege return and this is final text
> >
> >
> > PDF shows:
> > 3 carriage return and this is text.
> >
> > 2 Carriege return and this is final text
> >
> >
> > NO INITAL carriage return in PDF but it does leave once there is some text.
> > Is this the rendering problem or a bug'
> >
> > Note: I am not using RTRIM or TRIM anywhere.sql

pb with a trigger

hello,
I've a problem with a trigger :
this trigger update a file with an index field but it's return an error
: UPDATE stop because the Set option : "QUOTED_IDENTIFIER" return bad
parameters
for information, this is my trigger :
CREATE TRIGGER COOP_article ON F_ARTICLE FOR insert AS
SET NOCOUNT ON
set arithabort on
set concat_null_yields_null on
set quoted_identifier on
set ansi_nulls on
set ansi_padding on
set ansi_warnings on
set numeric_roundabort off
declare @.code varchar(17)
select @.code=ar_ref from inserted
begin
*/ replace if reference = 0 */
if @.code='0'
begin
update f_article set ar_ref='test' from f_article join
inserted on f_article.cbmarq=inserted.cbmarq
end
end
if you ave an idea...
thanks
philnedPerhaps the trigger was created with SET QUOTED_IDENTIFIER OFF. This
setting is remembered for subsequent trigger executions. Try:
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE TRIGGER COOP_article ON F_ARTICLE FOR insert AS
SET NOCOUNT ON
set arithabort on
set concat_null_yields_null on
set quoted_identifier on
set ansi_nulls on
set ansi_padding on
set ansi_warnings on
set numeric_roundabort off
declare @.code varchar(17)
select @.code=ar_ref from inserted
begin
*/ replace if reference = 0 */
if @.code='0'
begin
update f_article set ar_ref='test' from f_article join
inserted on f_article.cbmarq=inserted.cbmarq
end
end
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"philned" <philned@.wanadoo.fr> wrote in message
news:418783e6$0$9305$636a15ce@.news.free.fr...
> hello,
> I've a problem with a trigger :
> this trigger update a file with an index field but it's return an error :
> UPDATE stop because the Set option : "QUOTED_IDENTIFIER" return bad
> parameters
> for information, this is my trigger :
> CREATE TRIGGER COOP_article ON F_ARTICLE FOR insert AS
> SET NOCOUNT ON
> set arithabort on
> set concat_null_yields_null on
> set quoted_identifier on
> set ansi_nulls on
> set ansi_padding on
> set ansi_warnings on
> set numeric_roundabort off
> declare @.code varchar(17)
> select @.code=ar_ref from inserted
> begin
> */ replace if reference = 0 */
> if @.code='0'
> begin
> update f_article set ar_ref='test' from f_article join
> inserted on f_article.cbmarq=inserted.cbmarq
> end
> end
>
> if you ave an idea...
> thanks
> philnedsql

pb with a trigger

hello,
I've a problem with a trigger :
this trigger update a file with an index field but it's return an error
: UPDATE stop because the Set option : "QUOTED_IDENTIFIER" return bad
parameters
for information, this is my trigger :
CREATE TRIGGER COOP_article ON F_ARTICLE FOR insert AS
SET NOCOUNT ON
set arithabort on
set concat_null_yields_null on
set quoted_identifier on
set ansi_nulls on
set ansi_padding on
set ansi_warnings on
set numeric_roundabort off
declare @.code varchar(17)
select @.code=ar_ref from inserted
begin
*/ replace if reference = 0 */
if @.code='0'
begin
update f_article set ar_ref='test' from f_article join
inserted on f_article.cbmarq=inserted.cbmarq
end
end
if you ave an idea...
thanks
philnedPerhaps the trigger was created with SET QUOTED_IDENTIFIER OFF. This
setting is remembered for subsequent trigger executions. Try:
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE TRIGGER COOP_article ON F_ARTICLE FOR insert AS
SET NOCOUNT ON
set arithabort on
set concat_null_yields_null on
set quoted_identifier on
set ansi_nulls on
set ansi_padding on
set ansi_warnings on
set numeric_roundabort off
declare @.code varchar(17)
select @.code=ar_ref from inserted
begin
*/ replace if reference = 0 */
if @.code='0'
begin
update f_article set ar_ref='test' from f_article join
inserted on f_article.cbmarq=inserted.cbmarq
end
end
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"philned" <philned@.wanadoo.fr> wrote in message
news:418783e6$0$9305$636a15ce@.news.free.fr...
> hello,
> I've a problem with a trigger :
> this trigger update a file with an index field but it's return an error :
> UPDATE stop because the Set option : "QUOTED_IDENTIFIER" return bad
> parameters
> for information, this is my trigger :
> CREATE TRIGGER COOP_article ON F_ARTICLE FOR insert AS
> SET NOCOUNT ON
> set arithabort on
> set concat_null_yields_null on
> set quoted_identifier on
> set ansi_nulls on
> set ansi_padding on
> set ansi_warnings on
> set numeric_roundabort off
> declare @.code varchar(17)
> select @.code=ar_ref from inserted
> begin
> */ replace if reference = 0 */
> if @.code='0'
> begin
> update f_article set ar_ref='test' from f_article join
> inserted on f_article.cbmarq=inserted.cbmarq
> end
> end
>
> if you ave an idea...
> thanks
> philned

pb with a trigger

hello,
I've a problem with a trigger :
this trigger update a file with an index field but it's return an error
: UPDATE stop because the Set option : "QUOTED_IDENTIFIER" return bad
parameters
for information, this is my trigger :
CREATE TRIGGER COOP_article ON F_ARTICLE FOR insert AS
SET NOCOUNT ON
set arithabort on
set concat_null_yields_null on
set quoted_identifier on
set ansi_nulls on
set ansi_padding on
set ansi_warnings on
set numeric_roundabort off
declare @.code varchar(17)
select @.code=ar_ref from inserted
begin
*/ replace if reference = 0 */
if @.code='0'
begin
update f_article set ar_ref='test' from f_article join
inserted on f_article.cbmarq=inserted.cbmarq
end
end
if you ave an idea...
thanks
philned
Perhaps the trigger was created with SET QUOTED_IDENTIFIER OFF. This
setting is remembered for subsequent trigger executions. Try:
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE TRIGGER COOP_article ON F_ARTICLE FOR insert AS
SET NOCOUNT ON
set arithabort on
set concat_null_yields_null on
set quoted_identifier on
set ansi_nulls on
set ansi_padding on
set ansi_warnings on
set numeric_roundabort off
declare @.code varchar(17)
select @.code=ar_ref from inserted
begin
*/ replace if reference = 0 */
if @.code='0'
begin
update f_article set ar_ref='test' from f_article join
inserted on f_article.cbmarq=inserted.cbmarq
end
end
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"philned" <philned@.wanadoo.fr> wrote in message
news:418783e6$0$9305$636a15ce@.news.free.fr...
> hello,
> I've a problem with a trigger :
> this trigger update a file with an index field but it's return an error :
> UPDATE stop because the Set option : "QUOTED_IDENTIFIER" return bad
> parameters
> for information, this is my trigger :
> CREATE TRIGGER COOP_article ON F_ARTICLE FOR insert AS
> SET NOCOUNT ON
> set arithabort on
> set concat_null_yields_null on
> set quoted_identifier on
> set ansi_nulls on
> set ansi_padding on
> set ansi_warnings on
> set numeric_roundabort off
> declare @.code varchar(17)
> select @.code=ar_ref from inserted
> begin
> */ replace if reference = 0 */
> if @.code='0'
> begin
> update f_article set ar_ref='test' from f_article join
> inserted on f_article.cbmarq=inserted.cbmarq
> end
> end
>
> if you ave an idea...
> thanks
> philned

pb with a trigger

hello,

I've a problem with a trigger :
this trigger update a file with an index field but it's return an error
: UPDATE stop because the Set option : "QUOTED_IDENTIFIER" return bad
parameters

for information, this is my trigger :

CREATE TRIGGER COOP_article ON F_ARTICLE FOR insert AS
SET NOCOUNT ON
set arithabort on
set concat_null_yields_null on
set quoted_identifier on
set ansi_nulls on
set ansi_padding on
set ansi_warnings on
set numeric_roundabort off
declare @.code varchar(17)
select @.code=ar_ref from inserted
begin
*/ replace if reference = 0 */
if @.code='0'
begin

update f_article set ar_ref='test' from f_article join
inserted on f_article.cbmarq=inserted.cbmarq
end
end

if you ave an idea...
thanks
philned[posted and mailed, please reply in news]

philned (philned@.wanadoo.fr) writes:
> I've a problem with a trigger :
> this trigger update a file with an index field but it's return an error
>: UPDATE stop because the Set option : "QUOTED_IDENTIFIER" return bad
> parameters

This is because there is an indexed view or an indexed computed
column some where.

Putting all those SET commands in the trigger is not a good idea, because
it may cause recompilations which can be expensive performancewise.

Instead make sure that you issue a SET ARITHABORT ON when you connect.
You can also set this default for your database, or even your setver.

As for QUOTED_IDENTIFIER, this setting is a bit special. It doesn't
help if you set it in the trigger, because it is the setting of
QUOTED_IDENTIFER when the trigger was created that applies. This also
applies to the setting ANSI_NULLS.

There are some tools that unfortunately turn of QUOTED_IDENTIFIER by
default. The command-line tool OSQL is one of them. Use -I with OSQL
to have QUOTED_IDENTIFIER on. The other is Enterprise Manger, but you
should not use EM for editing SQL Code, use Query Analyzer instead.

In short: you need to reload the trigger with QUOTED_IDENTIIER on.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

pb with "QUOTED_IDENTIFIER"

hello,
I've a problem with a trigger :
this trigger update a file with an index field but it's return an error
: UPDATE stop because the Set option : "QUOTED_IDENTIFIER" return bad
parameters
for information, this is my trigger :
CREATE TRIGGER COOP_article ON F_ARTICLE FOR insert AS
SET NOCOUNT ON
set arithabort on
set concat_null_yields_null on
set quoted_identifier on
set ansi_nulls on
set ansi_padding on
set ansi_warnings on
set numeric_roundabort off
declare @.code varchar(17)
select @.code=ar_ref from inserted
begin
*/ replace if reference = 0 */
if @.code='0'
begin
update f_article set ar_ref='test' from f_article join
inserted on f_article.cbmarq=inserted.cbmarq
end
end
if you ave an idea...
thanks
philned
Why are you setting quoted identifiers on? You don't seem to need it.
Same goes for most of the set statements.
"philned" wrote:

> hello,
> I've a problem with a trigger :
> this trigger update a file with an index field but it's return an error
> : UPDATE stop because the Set option : "QUOTED_IDENTIFIER" return bad
> parameters
> for information, this is my trigger :
> CREATE TRIGGER COOP_article ON F_ARTICLE FOR insert AS
> SET NOCOUNT ON
> set arithabort on
> set concat_null_yields_null on
> set quoted_identifier on
> set ansi_nulls on
> set ansi_padding on
> set ansi_warnings on
> set numeric_roundabort off
> declare @.code varchar(17)
> select @.code=ar_ref from inserted
> begin
> */ replace if reference = 0 */
> if @.code='0'
> begin
> update f_article set ar_ref='test' from f_article join
> inserted on f_article.cbmarq=inserted.cbmarq
> end
> end
>
> if you ave an idea...
> thanks
> philned
>

Pattern Matching - Searching for Numeric or Alpha or Alpha-Numeric characters in a string

Hi,

I was trying to find numeric characters in a field of nvarchar. I looked this up in HELP.

Wildcard

Meaning

%

Any string of zero or more characters.

_

Any single character.

[ ]

Any single character within the specified range (for example, [a-f]) or set (for example, [abcdef]).

Any single character not within the specified range (for example, [^a - f]) or set (for example, [^abcdef]).

Nowhere in the examples below it in Help was it explicitly detailed that a user could do this.

In MS Access the # can be substituted for any numeric character such that I could do a WHERE clause:

WHERE
Gift_Date NOT LIKE "####*"

After looking at the above for the [ ] wildcard, it became clear that I could subsitute [0-9] for #:

WHERE
Gift_Date NOT LIKE '[0-9][0-9][0-9][0-9]%'

using single quotes and the % wildcard instead of Access' double quotes and * wildcard.

Just putting this out there for anybody else that is new to SQL, like me.

Regards,

Patrick Briggs,
Pasadena, CA

Patrick Briggs wrote:

In MS Access the # can be substituted for any numeric character such that I could do a WHERE clause:

WHERE
Gift_Date NOT LIKE "####*"

After looking at the above for the [ ] wildcard, it became clear that I could subsitute [0-9] for #:

WHERE
Gift_Date NOT LIKE '[0-9][0-9][0-9][0-9]%'

It is not quite the same. The LIKE pattern in TSQL will also match values like '0123A' and '1248mkfaliw' whereas the one in Access doesn't. The correct way to specify it is to do below:

WHERE Gift_Date NOT LIKE replicate('[0-9]', 4)

The replicate function just simplifies the repetition of the same pattern multiple times.

sql

PATINDEX to Retrieve data from text field

I am trying to retrieve the data from a table that has text datatype. I just
need to
pull 5 digit numeric value from there which can later be matched with
another table
that has this 5 digit key. Let us assume the table name is Tab1. There are
two
columns col1 containing RecordId and col2 containing text data. I have
created some dummy data to explain my needs:
Col1 Col2
1001 @.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC INC.@.SOMETHING
1002 @.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF INC.@.SOMETHING
1003 @.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI INC.@.ANYTHING
1004 @.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM INC.@.ANYTHING
1005 @.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING
1006 @.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING
1007 @.:92A:CUST8@.ANYTHING
If I use the following Query which needs to be tuned up to get the right
resultset:
SELECT SUBSTRING(col2, PATINDEX('%@.:97_:%', col2)+14, 5)
from tab1
where PATINDEX('%@.:97_:%', col2) > 0
I get the following results: The top two results are correct but others are
not.
col1 col2
-- --
1001 11221
1002 11229
1003 1233@.
1004 333@.C
1005 IENT
1006 AME I
I need the following resultset from the above data:
Col1 Col2
-- --
1001 11221
1002 11229
1003 11233
1004 11333
1005 22333
Col1 Id 1006 does not have the 5 digit numeric value so it is not required
in the
resultset. Id 1007 does not have :97_C: so this is also not required in the
resultset
too. I will appreciate your help. Thanks in advance. Fraz
Fraz
Look at this example helps you
CREATE FUNCTION dbo.CleanChars
(@.str VARCHAR(8000), @.validchars VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^' + @.validchars +
']%',@.str), 1) ,'')
RETURN @.str
END
GO
CREATE TABLE sometable
(namestr VARCHAR(20) PRIMARY KEY)
INSERT INTO sometable VALUES ('AB-C123')
INSERT INTO sometable VALUES ('A,B,C')
SELECT namestr,
dbo.CleanChars(namestr,'A-Z 0-9')
FROM sometable
drop table sometable
drop function dbo.CleanChars
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:B964C72E-D1A4-4906-A105-E1D87A2F29D6@.microsoft.com...
> I am trying to retrieve the data from a table that has text datatype. I
just
> need to
> pull 5 digit numeric value from there which can later be matched with
> another table
> that has this 5 digit key. Let us assume the table name is Tab1. There are
> two
> columns col1 containing RecordId and col2 containing text data. I have
> created some dummy data to explain my needs:
> Col1 Col2
> 1001 @.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
INC.@.SOMETHING
> 1002 @.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
INC.@.SOMETHING
> 1003 @.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI INC.@.ANYTHING
> 1004 @.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM INC.@.ANYTHING
> 1005 @.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING
> 1006 @.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING
> 1007 @.:92A:CUST8@.ANYTHING
> If I use the following Query which needs to be tuned up to get the right
> resultset:
> SELECT SUBSTRING(col2, PATINDEX('%@.:97_:%', col2)+14, 5)
> from tab1
> where PATINDEX('%@.:97_:%', col2) > 0
> I get the following results: The top two results are correct but others
are
> not.
> col1 col2
> -- --
> 1001 11221
> 1002 11229
> 1003 1233@.
> 1004 333@.C
> 1005 IENT
> 1006 AME I
> I need the following resultset from the above data:
> Col1 Col2
> -- --
> 1001 11221
> 1002 11229
> 1003 11233
> 1004 11333
> 1005 22333
> Col1 Id 1006 does not have the 5 digit numeric value so it is not required
> in the
> resultset. Id 1007 does not have :97_C: so this is also not required in
the
> resultset
> too. I will appreciate your help. Thanks in advance. Fraz
|||Uri: Thanks for your valuable input. This is a nice function which I am
trying to see if it can fit in my needs. If you could help little more by
showing how I can check to see the 5 digit numbers (11233) between this data
@.:97B:/XX022511233@.CLIENT. The position is always not the same. So by getting
@.:97_: we can get first position and by next "@." we can get second position.
Now I know that my data is in between first and second position and by using
RIGHT function I can get the 5 right digits. Thanks again...Fraz
"Uri Dimant" wrote:

> Fraz
> Look at this example helps you
> CREATE FUNCTION dbo.CleanChars
> (@.str VARCHAR(8000), @.validchars VARCHAR(8000))
> RETURNS VARCHAR(8000)
> BEGIN
> WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
> SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^' + @.validchars +
> ']%',@.str), 1) ,'')
> RETURN @.str
> END
> GO
> CREATE TABLE sometable
> (namestr VARCHAR(20) PRIMARY KEY)
> INSERT INTO sometable VALUES ('AB-C123')
> INSERT INTO sometable VALUES ('A,B,C')
> SELECT namestr,
> dbo.CleanChars(namestr,'A-Z 0-9')
> FROM sometable
>
> drop table sometable
> drop function dbo.CleanChars
> "Fraz" <Fraz@.discussions.microsoft.com> wrote in message
> news:B964C72E-D1A4-4906-A105-E1D87A2F29D6@.microsoft.com...
> just
> INC.@.SOMETHING
> INC.@.SOMETHING
> are
> the
>
>
|||Hi Fraz,
On Thu, 14 Apr 2005 06:30:05 -0700, Fraz wrote:
(snip)
>I need the following resultset from the above data:
>Col1 Col2
>-- --
>1001 11221
>1002 11229
>1003 11233
>1004 11333
>1005 22333
(snip)
Try the following (note: I added a test case to check that I return the
five digits preceding "@." AFTER the "@.:97_:" marker, not simply the
first five digits followed by "@.").
-- Set up test table and fill it with some rows
create table tab1 (col1 int not null primary key, col2 varchar(200))
go
insert into tab1
select 1001, '@.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
INC.@.SOMETHING'
union all
select 1002, '@.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
INC.@.SOMETHING'
union all
select 1003, '@.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI
INC.@.ANYTHING'
union all
select 1004, '@.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM
INC.@.ANYTHING'
union all
select 1005, '@.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING'
union all
select 1006, '@.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING'
union all
select 1007, '@.:92A:CUST8@.ANYTHING'
union all
select 1008, '@.:92A:CUST44444@.:97B:/XX022511233@.CLIENT NAME IS GHI
INC.@.ANYTHING'
go
-- Here's the code:
SELECT col1,
SUBSTRING(col2,
PATINDEX('%[0-9][0-9][0-9][0-9][0-9]@.%',
SUBSTRING(col2,
PATINDEX('%@.:97_:%', col2),
LEN(col2)))
+ PATINDEX('%@.:97_:%', col2)
- 1,
5)
FROM tab1
WHERE col2 LIKE '%@.:97_:%[0-9][0-9][0-9][0-9][0-9]@.%'
go
-- Done. Now cleanup.
drop table tab1
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hello Hugo,
Your code has worked excellently. Most of the 5 digit numbers were correct
except for a few records that were very long and numbers were not correct. I
have dealt with it separately. Thanks a lot for your help. Cheers... Fraz
"Hugo Kornelis" wrote:

> Hi Fraz,
> On Thu, 14 Apr 2005 06:30:05 -0700, Fraz wrote:
> (snip)
> (snip)
> Try the following (note: I added a test case to check that I return the
> five digits preceding "@." AFTER the "@.:97_:" marker, not simply the
> first five digits followed by "@.").
> -- Set up test table and fill it with some rows
> create table tab1 (col1 int not null primary key, col2 varchar(200))
> go
> insert into tab1
> select 1001, '@.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
> INC.@.SOMETHING'
> union all
> select 1002, '@.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
> INC.@.SOMETHING'
> union all
> select 1003, '@.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI
> INC.@.ANYTHING'
> union all
> select 1004, '@.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM
> INC.@.ANYTHING'
> union all
> select 1005, '@.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING'
> union all
> select 1006, '@.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING'
> union all
> select 1007, '@.:92A:CUST8@.ANYTHING'
> union all
> select 1008, '@.:92A:CUST44444@.:97B:/XX022511233@.CLIENT NAME IS GHI
> INC.@.ANYTHING'
> go
> -- Here's the code:
> SELECT col1,
> SUBSTRING(col2,
> PATINDEX('%[0-9][0-9][0-9][0-9][0-9]@.%',
> SUBSTRING(col2,
> PATINDEX('%@.:97_:%', col2),
> LEN(col2)))
> + PATINDEX('%@.:97_:%', col2)
> - 1,
> 5)
> FROM tab1
> WHERE col2 LIKE '%@.:97_:%[0-9][0-9][0-9][0-9][0-9]@.%'
> go
>
> -- Done. Now cleanup.
> drop table tab1
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Fri, 22 Apr 2005 14:21:03 -0700, Fraz wrote:

>Hello Hugo,
>Your code has worked excellently. Most of the 5 digit numbers were correct
>except for a few records that were very long and numbers were not correct. I
>have dealt with it separately. Thanks a lot for your help. Cheers... Fraz
Hi Fraz,
Good to hear that it worked for you. Thanks for reporting back!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

PATINDEX to Retrieve data from text field

I am trying to retrieve the data from a table that has text datatype. I just
need to
pull 5 digit numeric value from there which can later be matched with
another table
that has this 5 digit key. Let us assume the table name is Tab1. There are
two
columns col1 containing RecordId and col2 containing text data. I have
created some dummy data to explain my needs:
Col1 Col2
1001 @.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC INC.@.SOMETHI
NG
1002 @.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF INC.@.SOMETHING
1003 @.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI INC.@.ANYTHING
1004 @.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM INC.@.ANYTHING
1005 @.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING
1006 @.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING
1007 @.:92A:CUST8@.ANYTHING
If I use the following Query which needs to be tuned up to get the right
resultset:
SELECT SUBSTRING(col2, PATINDEX('%@.:97_:%', col2)+14, 5)
from tab1
where PATINDEX('%@.:97_:%', col2) > 0
I get the following results: The top two results are correct but others are
not.
col1 col2
-- --
1001 11221
1002 11229
1003 1233@.
1004 333@.C
1005 IENT
1006 AME I
I need the following resultset from the above data:
Col1 Col2
-- --
1001 11221
1002 11229
1003 11233
1004 11333
1005 22333
Col1 Id 1006 does not have the 5 digit numeric value so it is not required
in the
resultset. Id 1007 does not have :97_C: so this is also not required in the
resultset
too. I will appreciate your help. Thanks in advance. FrazFraz
Look at this example helps you
CREATE FUNCTION dbo.CleanChars
(@.str VARCHAR(8000), @.validchars VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^' + @.validchars +
']%',@.str), 1) ,'')
RETURN @.str
END
GO
CREATE TABLE sometable
(namestr VARCHAR(20) PRIMARY KEY)
INSERT INTO sometable VALUES ('AB-C123')
INSERT INTO sometable VALUES ('A,B,C')
SELECT namestr,
dbo.CleanChars(namestr,'A-Z 0-9')
FROM sometable
drop table sometable
drop function dbo.CleanChars
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:B964C72E-D1A4-4906-A105-E1D87A2F29D6@.microsoft.com...
> I am trying to retrieve the data from a table that has text datatype. I
just
> need to
> pull 5 digit numeric value from there which can later be matched with
> another table
> that has this 5 digit key. Let us assume the table name is Tab1. There are
> two
> columns col1 containing RecordId and col2 containing text data. I have
> created some dummy data to explain my needs:
> Col1 Col2
> 1001 @.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
INC.@.SOMETHING
> 1002 @.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
INC.@.SOMETHING
> 1003 @.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI INC.@.ANYTHING
> 1004 @.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM INC.@.ANYTHING
> 1005 @.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING
> 1006 @.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING
> 1007 @.:92A:CUST8@.ANYTHING
> If I use the following Query which needs to be tuned up to get the right
> resultset:
> SELECT SUBSTRING(col2, PATINDEX('%@.:97_:%', col2)+14, 5)
> from tab1
> where PATINDEX('%@.:97_:%', col2) > 0
> I get the following results: The top two results are correct but others
are
> not.
> col1 col2
> -- --
> 1001 11221
> 1002 11229
> 1003 1233@.
> 1004 333@.C
> 1005 IENT
> 1006 AME I
> I need the following resultset from the above data:
> Col1 Col2
> -- --
> 1001 11221
> 1002 11229
> 1003 11233
> 1004 11333
> 1005 22333
> Col1 Id 1006 does not have the 5 digit numeric value so it is not required
> in the
> resultset. Id 1007 does not have :97_C: so this is also not required in
the
> resultset
> too. I will appreciate your help. Thanks in advance. Fraz|||Uri: Thanks for your valuable input. This is a nice function which I am
trying to see if it can fit in my needs. If you could help little more by
showing how I can check to see the 5 digit numbers (11233) between this data
@.:97B:/XX022511233@.CLIENT. The position is always not the same. So by gettin
g
@.:97_: we can get first position and by next "@." we can get second position.
Now I know that my data is in between first and second position and by using
RIGHT function I can get the 5 right digits. Thanks again...Fraz
"Uri Dimant" wrote:

> Fraz
> Look at this example helps you
> CREATE FUNCTION dbo.CleanChars
> (@.str VARCHAR(8000), @.validchars VARCHAR(8000))
> RETURNS VARCHAR(8000)
> BEGIN
> WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
> SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^' + @.validchars
+
> ']%',@.str), 1) ,'')
> RETURN @.str
> END
> GO
> CREATE TABLE sometable
> (namestr VARCHAR(20) PRIMARY KEY)
> INSERT INTO sometable VALUES ('AB-C123')
> INSERT INTO sometable VALUES ('A,B,C')
> SELECT namestr,
> dbo.CleanChars(namestr,'A-Z 0-9')
> FROM sometable
>
> drop table sometable
> drop function dbo.CleanChars
> "Fraz" <Fraz@.discussions.microsoft.com> wrote in message
> news:B964C72E-D1A4-4906-A105-E1D87A2F29D6@.microsoft.com...
> just
> INC.@.SOMETHING
> INC.@.SOMETHING
> are
> the
>
>|||Hi Fraz,
On Thu, 14 Apr 2005 06:30:05 -0700, Fraz wrote:
(snip)
>I need the following resultset from the above data:
>Col1 Col2
>-- --
>1001 11221
>1002 11229
>1003 11233
>1004 11333
>1005 22333
(snip)
Try the following (note: I added a test case to check that I return the
five digits preceding "@." AFTER the "@.:97_:" marker, not simply the
first five digits followed by "@.").
-- Set up test table and fill it with some rows
create table tab1 (col1 int not null primary key, col2 varchar(200))
go
insert into tab1
select 1001, '@.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
INC.@.SOMETHING'
union all
select 1002, '@.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
INC.@.SOMETHING'
union all
select 1003, '@.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI
INC.@.ANYTHING'
union all
select 1004, '@.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM
INC.@.ANYTHING'
union all
select 1005, '@.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING'
union all
select 1006, '@.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING'
union all
select 1007, '@.:92A:CUST8@.ANYTHING'
union all
select 1008, '@.:92A:CUST44444@.:97B:/XX022511233@.CLIENT NAME IS GHI
INC.@.ANYTHING'
go
-- Here's the code:
SELECT col1,
SUBSTRING(col2,
PATINDEX('%[0-9][0-9][0-9][0-9][0-9]@.%',
SUBSTRING(col2,
PATINDEX('%@.:97_:%', col2),
LEN(col2)))
+ PATINDEX('%@.:97_:%', col2)
- 1,
5)
FROM tab1
WHERE col2 LIKE '%@.:97_:%[0-9][0-9][0-9][0-9][0-9]@.%'
go
-- Done. Now cleanup.
drop table tab1
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
Your code has worked excellently. Most of the 5 digit numbers were correct
except for a few records that were very long and numbers were not correct. I
have dealt with it separately. Thanks a lot for your help. Cheers... Fraz
"Hugo Kornelis" wrote:

> Hi Fraz,
> On Thu, 14 Apr 2005 06:30:05 -0700, Fraz wrote:
> (snip)
> (snip)
> Try the following (note: I added a test case to check that I return the
> five digits preceding "@." AFTER the "@.:97_:" marker, not simply the
> first five digits followed by "@.").
> -- Set up test table and fill it with some rows
> create table tab1 (col1 int not null primary key, col2 varchar(200))
> go
> insert into tab1
> select 1001, '@.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
> INC.@.SOMETHING'
> union all
> select 1002, '@.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
> INC.@.SOMETHING'
> union all
> select 1003, '@.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI
> INC.@.ANYTHING'
> union all
> select 1004, '@.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM
> INC.@.ANYTHING'
> union all
> select 1005, '@.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING'
> union all
> select 1006, '@.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING'
> union all
> select 1007, '@.:92A:CUST8@.ANYTHING'
> union all
> select 1008, '@.:92A:CUST44444@.:97B:/XX022511233@.CLIENT NAME IS GHI
> INC.@.ANYTHING'
> go
> -- Here's the code:
> SELECT col1,
> SUBSTRING(col2,
> PATINDEX('%[0-9][0-9][0-9][0-9][0-9]@.
%',
> SUBSTRING(col2,
> PATINDEX('%@.:97_:%', col2),
> LEN(col2)))
> + PATINDEX('%@.:97_:%', col2)
> - 1,
> 5)
> FROM tab1
> WHERE col2 LIKE '%@.:97_:%[0-9][0-9][0-9][0-9][0-9]@.%'
> go
>
> -- Done. Now cleanup.
> drop table tab1
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Fri, 22 Apr 2005 14:21:03 -0700, Fraz wrote:

>Hello Hugo,
>Your code has worked excellently. Most of the 5 digit numbers were correct
>except for a few records that were very long and numbers were not correct.
I
>have dealt with it separately. Thanks a lot for your help. Cheers... Fraz
Hi Fraz,
Good to hear that it worked for you. Thanks for reporting back!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

PATINDEX and CHARINDEX

Hello
I have a table in my database , in one field i have data like this
(230+365+651+695) varchar(100)
I want to use the any SQL function to return the data in form of rows from
the above field.
Result should look like
230
365
651
695
Anyone can help in this regard'?
Thanks in advance.Here's one way to do it using an auxiliary table of numbers:
SET NOCOUNT ON;
USE tempdb; -- specify your user db here
GO
IF OBJECT_ID('dbo.Arrays') IS NOT NULL
DROP TABLE dbo.Arrays;
GO
CREATE TABLE dbo.Arrays
(
arrid VARCHAR(5) NOT NULL PRIMARY KEY,
arr VARCHAR(1000) NOT NULL
);
INSERT INTO dbo.Arrays VALUES('A', '230+365+651+695');
INSERT INTO dbo.Arrays VALUES('B', '1+23+456');
GO
-- Code to create and populate the auxiliary table of numbers:
IF OBJECT_ID('dbo.Nums') IS NOT NULL
DROP TABLE dbo.Nums;
GO
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @.max AS INT, @.rc AS INT;
SET @.max = 8000; -- adjust @.max to your needs
SET @.rc = 1;
INSERT INTO dbo.Nums VALUES(1);
WHILE @.rc * 2 <= @.max
BEGIN
INSERT INTO dbo.Nums SELECT n + @.rc FROM dbo.Nums;
SET @.rc = @.rc * 2;
END
INSERT INTO dbo.Nums SELECT n + @.rc FROM dbo.Nums WHERE n + @.rc <= @.max;
GO
-- Query that splits arrays
SELECT A.arrid,
Nums.n - LEN(REPLACE(LEFT(A.arr, Nums.n), '+', '')) + 1 AS pos,
CAST(SUBSTRING(A.arr, Nums.n,
CHARINDEX('+', A.arr + '+', Nums.n) - Nums.n)
AS INT) AS element
FROM dbo.Arrays AS A
JOIN dbo.Nums
ON Nums.n <= LEN(A.arr) AND SUBSTRING('+' + A.arr, Nums.n, 1) = '+';
GO
Output:
arrid pos element
-- -- --
A 1 230
A 2 365
A 3 651
A 4 695
B 1 1
B 2 23
B 3 456
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"mukki_neo" <u20644@.uwe> wrote in message news:5e741dc37de34@.uwe...
> Hello
> I have a table in my database , in one field i have data like this
> (230+365+651+695) varchar(100)
> I want to use the any SQL function to return the data in form of rows from
> the above field.
> Result should look like
> 230
> 365
> 651
> 695
> Anyone can help in this regard'?
> Thanks in advance.|||Thanks a lot for quick response.
It just work fine with my Database, but what if i have a value of
"Alphanumeric" in this field... like
(DO-903+DP-366+DP-659+TM-TEMP)
same for other rows corresponding columns
i want to show it like
DO-903
DP-366
DP-659
TM-TEMP
Any help please?
Itzik Ben-Gan wrote:
>Here's one way to do it using an auxiliary table of numbers:
>SET NOCOUNT ON;
>USE tempdb; -- specify your user db here
>GO
>IF OBJECT_ID('dbo.Arrays') IS NOT NULL
> DROP TABLE dbo.Arrays;
>GO
>CREATE TABLE dbo.Arrays
>(
> arrid VARCHAR(5) NOT NULL PRIMARY KEY,
> arr VARCHAR(1000) NOT NULL
> );
>INSERT INTO dbo.Arrays VALUES('A', '230+365+651+695');
>INSERT INTO dbo.Arrays VALUES('B', '1+23+456');
>GO
>-- Code to create and populate the auxiliary table of numbers:
>IF OBJECT_ID('dbo.Nums') IS NOT NULL
> DROP TABLE dbo.Nums;
>GO
>CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
>DECLARE @.max AS INT, @.rc AS INT;
>SET @.max = 8000; -- adjust @.max to your needs
>SET @.rc = 1;
>INSERT INTO dbo.Nums VALUES(1);
>WHILE @.rc * 2 <= @.max
>BEGIN
> INSERT INTO dbo.Nums SELECT n + @.rc FROM dbo.Nums;
> SET @.rc = @.rc * 2;
>END
>INSERT INTO dbo.Nums SELECT n + @.rc FROM dbo.Nums WHERE n + @.rc <= @.max;
>GO
>-- Query that splits arrays
>SELECT A.arrid,
> Nums.n - LEN(REPLACE(LEFT(A.arr, Nums.n), '+', '')) + 1 AS pos,
> CAST(SUBSTRING(A.arr, Nums.n,
> CHARINDEX('+', A.arr + '+', Nums.n) - Nums.n)
> AS INT) AS element
>FROM dbo.Arrays AS A
> JOIN dbo.Nums
> ON Nums.n <= LEN(A.arr) AND SUBSTRING('+' + A.arr, Nums.n, 1) = '+';
>GO
>Output:
>arrid pos element
>-- -- --
>A 1 230
>A 2 365
>A 3 651
>A 4 695
>B 1 1
>B 2 23
>B 3 456
>
>[quoted text clipped - 12 lines]|||I changed the CAST from INT to VARCHAR, and it works fine, am i right?
Itzik Ben-Gan wrote:
>Here's one way to do it using an auxiliary table of numbers:
>SET NOCOUNT ON;
>USE tempdb; -- specify your user db here
>GO
>IF OBJECT_ID('dbo.Arrays') IS NOT NULL
> DROP TABLE dbo.Arrays;
>GO
>CREATE TABLE dbo.Arrays
>(
> arrid VARCHAR(5) NOT NULL PRIMARY KEY,
> arr VARCHAR(1000) NOT NULL
> );
>INSERT INTO dbo.Arrays VALUES('A', '230+365+651+695');
>INSERT INTO dbo.Arrays VALUES('B', '1+23+456');
>GO
>-- Code to create and populate the auxiliary table of numbers:
>IF OBJECT_ID('dbo.Nums') IS NOT NULL
> DROP TABLE dbo.Nums;
>GO
>CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
>DECLARE @.max AS INT, @.rc AS INT;
>SET @.max = 8000; -- adjust @.max to your needs
>SET @.rc = 1;
>INSERT INTO dbo.Nums VALUES(1);
>WHILE @.rc * 2 <= @.max
>BEGIN
> INSERT INTO dbo.Nums SELECT n + @.rc FROM dbo.Nums;
> SET @.rc = @.rc * 2;
>END
>INSERT INTO dbo.Nums SELECT n + @.rc FROM dbo.Nums WHERE n + @.rc <= @.max;
>GO
>-- Query that splits arrays
>SELECT A.arrid,
> Nums.n - LEN(REPLACE(LEFT(A.arr, Nums.n), '+', '')) + 1 AS pos,
> CAST(SUBSTRING(A.arr, Nums.n,
> CHARINDEX('+', A.arr + '+', Nums.n) - Nums.n)
> AS INT) AS element
>FROM dbo.Arrays AS A
> JOIN dbo.Nums
> ON Nums.n <= LEN(A.arr) AND SUBSTRING('+' + A.arr, Nums.n, 1) = '+';
>GO
>Output:
>arrid pos element
>-- -- --
>A 1 230
>A 2 365
>A 3 651
>A 4 695
>B 1 1
>B 2 23
>B 3 456
>
>[quoted text clipped - 12 lines]|||No need to cast it if you want to keep it a character string.
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"mukki_neo" <u20644@.uwe> wrote in message news:5e757a18834f7@.uwe...
>I changed the CAST from INT to VARCHAR, and it works fine, am i right?
> Itzik Ben-Gan wrote:

Path to a "personal knowledge database"

Greetings,

I have to admit that I'm still a beginner in the database field, but I'm
actively studying, and this is why I will be utterly grateful for the
proper, accurate, and wise guidance to the right direction or specific
paths of database studies.

I simply want to make my first major database project a "personal
knowledge database" or a "personal encyclopedia", so to speak. By this I
mean that I want to gather diverse, multi-format bits of knowledge into
a single database divided into major categories, sub-categories, and so
on. For example, I want to scan collected articles from newspapers &
magazine and save them as images, type hand-written notes into the
computer and save them as MS Word or Text files, save audio files with
searchable keywords, even save searchable MS OneNote audio & text files,
all into directories & sub-directories. Then I want to be able to search
all these files for specific words or subjects; sort them according to
date, subject, etc; update, add to or append, edit, or even cut & paste
(within) any of those records in the database, which rely on easily
customizable formats like text & images (in contrast with audio files or
OneNote files, for example).

I also want to prevent wasting time in the future, by avoiding
repetitive, same-keywords typing for every non-text new entry, like an
image or audio file, by having a customizable keyword list open (by
right clicking for example), where one could mark all the relevant
keywords for the picture or audio file. Also have a short description
for each one of those entries when needed. And I want to be able to add
slick, good-looking graphics to the interface of the database and the
forms of the records.

Basically, my priorities include: saving time filling the database with
knowledge, quickly & accurately finding the future targets of any
search, presenting the search results in a highly comprehensible &
organized form, and a good looking interface & forms for every entry,
which are pleasing to the eye and encouraging to study.

I already use graphics applications for the "looks" part. So my question
is: what are the technologies or applications that I should learn by
heart and use in order to design such a database in the best way? Would
MS Access alone do it all for me? Or should I learn other technologies?

I'll be grateful for anyone taking the time to help. Thanks.

Sincerely,

Yasso

"Claiming that God does not exist because there are people suffering and
dying is like saying that barbers do not exist because there are people
with long hair! Truth is: they suffer because they did not find God or
do not go to Him, just like the others did not find a barber or do not
go to one."

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!If you are looking for something easy to start with, MS Access is
probably a good place to start. You will be able to find a google
group with plenty of help for this. Also, have a look at
www.mvps.org/access as this is where I learnt a lot of my MS Access
development and I recommend it very highly.

Yasso Picasso <yassopicophd@.hotmail.com> wrote in message news:<3fffe91b$0$70304$75868355@.news.frii.net>...
> Greetings,
> I have to admit that I'm still a beginner in the database field, but I'm
> actively studying, and this is why I will be utterly grateful for the
> proper, accurate, and wise guidance to the right direction or specific
> paths of database studies.
> I simply want to make my first major database project a "personal
> knowledge database" or a "personal encyclopedia", so to speak. By this I
> mean that I want to gather diverse, multi-format bits of knowledge into
> a single database divided into major categories, sub-categories, and so
> on. For example, I want to scan collected articles from newspapers &
> magazine and save them as images, type hand-written notes into the
> computer and save them as MS Word or Text files, save audio files with
> searchable keywords, even save searchable MS OneNote audio & text files,
> all into directories & sub-directories. Then I want to be able to search
> all these files for specific words or subjects; sort them according to
> date, subject, etc; update, add to or append, edit, or even cut & paste
> (within) any of those records in the database, which rely on easily
> customizable formats like text & images (in contrast with audio files or
> OneNote files, for example).
> I also want to prevent wasting time in the future, by avoiding
> repetitive, same-keywords typing for every non-text new entry, like an
> image or audio file, by having a customizable keyword list open (by
> right clicking for example), where one could mark all the relevant
> keywords for the picture or audio file. Also have a short description
> for each one of those entries when needed. And I want to be able to add
> slick, good-looking graphics to the interface of the database and the
> forms of the records.
> Basically, my priorities include: saving time filling the database with
> knowledge, quickly & accurately finding the future targets of any
> search, presenting the search results in a highly comprehensible &
> organized form, and a good looking interface & forms for every entry,
> which are pleasing to the eye and encouraging to study.
> I already use graphics applications for the "looks" part. So my question
> is: what are the technologies or applications that I should learn by
> heart and use in order to design such a database in the best way? Would
> MS Access alone do it all for me? Or should I learn other technologies?
> I'll be grateful for anyone taking the time to help. Thanks.
> Sincerely,
> Yasso
>
> "Claiming that God does not exist because there are people suffering and
> dying is like saying that barbers do not exist because there are people
> with long hair! Truth is: they suffer because they did not find God or
> do not go to Him, just like the others did not find a barber or do not
> go to one."
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Patching SQL server in the field?

I've been working with the DBAs to come up with a plan to Service pack and
patch our SQL servers in the field. I've search high and low and cannot find
any solid docs on if this is possible? supported or recommended? or how to d
o
it at all. I built a new test Software update package and it had only a hand
full of security updates for SQL. So i guess my questions are
1) Is this possible and are there documents to support it?
2) SQL service packs a built very differently then normal patches and
updates. Because of this is it not advisable to repackage them for SMS?
Thanks in Advance!!
DobolinaIt's possible with SQL Server 2000, but it uses some old installshield
technology so that you need to capture an answer file and use that (and be
pretty sure that your choises will work on each server where it will be
applied). I had some minor issues with this when I upgraded some 70-80
servers a couple of years ago.
With SQL 2005 Microsoft moved to Windows Installer, and it seems to be much
easier to upgrade. Last week I created an upgrade from SQL 2000 to SQL 2005
with SP2 that I'm able to run silently from the SMS client.
/Rune
"Bob Dobolina" <Bob Dobolina@.discussions.microsoft.com> wrote in message
news:215C80C3-06DF-4B40-A82A-37CF2877504D@.microsoft.com...
> I've been working with the DBAs to come up with a plan to Service pack and
> patch our SQL servers in the field. I've search high and low and cannot
> find
> any solid docs on if this is possible? supported or recommended? or how to
> do
> it at all. I built a new test Software update package and it had only a
> hand
> full of security updates for SQL. So i guess my questions are
> 1) Is this possible and are there documents to support it?
> 2) SQL service packs a built very differently then normal patches and
> updates. Because of this is it not advisable to repackage them for SMS?
> Thanks in Advance!!
> Dobolina|||Thanks Rune,
I too noticed the SP4 exe extracts into an old school directory structure.
Lucky we have a packaging wizard. I'll sick him on the task.
again, thanks!
"Rune Norberg" wrote:

> It's possible with SQL Server 2000, but it uses some old installshield
> technology so that you need to capture an answer file and use that (and be
> pretty sure that your choises will work on each server where it will be
> applied). I had some minor issues with this when I upgraded some 70-80
> servers a couple of years ago.
> With SQL 2005 Microsoft moved to Windows Installer, and it seems to be muc
h
> easier to upgrade. Last week I created an upgrade from SQL 2000 to SQL 200
5
> with SP2 that I'm able to run silently from the SMS client.
> /Rune
> "Bob Dobolina" <Bob Dobolina@.discussions.microsoft.com> wrote in message
> news:215C80C3-06DF-4B40-A82A-37CF2877504D@.microsoft.com...
>|||Hmmm, are you trying to do a repackage of this servicepack? I doubt that you
will get a good repacked solution as there are several *.sql scripts that is
run against to system databases to upgrade them.
/Rune
"Bob Dobolina" <BobDobolina@.discussions.microsoft.com> wrote in message
news:93931A57-7C13-4C22-88E2-9C7527D5EEC8@.microsoft.com...[vbcol=seagreen]
> Thanks Rune,
> I too noticed the SP4 exe extracts into an old school directory structure.
> Lucky we have a packaging wizard. I'll sick him on the task.
> again, thanks!
> "Rune Norberg" wrote:
>

Passwords

How would i set a Database password that NOONE could see the Database layouts or field names and Data....i mean obviously there are hackers that can get programs to "Bust" into Most database but in general how would i prevent someone from looking at the layout and information?

My next question is a tough one, I THINK......I have a front end of VB6,......i want the back end to be SQL Server or SQL Sever Express 2005 When someone goes to login into my software product i would like to check a DB of valid User Names and Passwords... Now if I SQL Server running on TRUE Server, ie Win 2003 Server....could 50 people login at the same time to my software from 50 different PC accross the network? Is that what SQL Server means by Connections? or could everyone use the "sa" login?

My application handles all the locking and unlocking of databases and records in the DB...so i dont have to rely on SQL Server for that....essentionally SQL Server is a Data DUmp for me.......what does everyone think?

Also.....can you re-distribute the MDF files aka..Databases....like you would Access without having SQL Server installed....? If your front end is VB6 and the MDF will that work? b/c the software validates its login with the list in the DB not what SQL Considers a Login....Just like you can take a Access DB named "Test.MDB" rename it "Test.xxx" and re-distribute that wo whoever you want and they dont need the Access runtime to operate the VB6 software....obviously Access uses JET ...what does SQL Server Use.....couldnt you do the same thing with SQL Server?

Thanks All!!!

moving the thread to the database engine forum.|||

If you distribute an application and database anyone having the binaries of those will be able to hack your application. Obfuscating might delay hacking.

What I have understood you will need SQL 2005 Express to be installed to attach mdf files.

According to documentation there is no limit for connections, you will need to configure it to allow remote connections though.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

I would use SQL servers ability to lock/unlock data rather than program it myself.

|||hi . did u get the answer?I have same problem . please send me if u find
|||

dragonsbb16 wrote:

1 How would i set a Database password that NOONE could see the Database layouts or field names and Data....i mean obviously there are hackers that can get programs to "Bust" into Most database but in general how would i prevent someone from looking at the layout and information?

2 My next question is a tough one, I THINK......I have a front end of VB6,......i want the back end to be SQL Server or SQL Sever Express 2005 When someone goes to login into my software product i would like to check a DB of valid User Names and Passwords... Now if I SQL Server running on TRUE Server, ie Win 2003 Server....could 50 people login at the same time to my software from 50 different PC accross the network? Is that what SQL Server means by Connections? or could everyone use the "sa" login?

My application handles all the locking and unlocking of databases and records in the DB...so i dont have to rely on SQL Server for that....essentionally SQL Server is a Data DUmp for me.......what does everyone think?

Also.....can you re-distribute the MDF files aka..Databases....like you would Access without having SQL Server installed....? If your front end is VB6 and the MDF will that work? b/c the software validates its login with the list in the DB not what SQL Considers a Login....Just like you can take a Access DB named "Test.MDB" rename it "Test.xxx" and re-distribute that wo whoever you want and they dont need the Access runtime to operate the VB6 software....obviously Access uses JET ...what does SQL Server Use.....couldnt you do the same thing with SQL Server?

Thanks All!!!

Edit : CONDENSED ANSWER - Lost long post due Session Timeout :(

1. You can grant to right to accounts to "view definitions" to various objects. Check "Permissions" in BOL

2. You can use ONE Account to Acces the DB and then check inside the DB if there is a User created for this person. This is used for most Forums for example. Security Tips : DONT give SA Accounts to everyone. 2. Storing Passwords in SQL Server you should take a look at Hashing (and Salting them)

3. Can be done. Ask yourself the important question : How likely is it that someone will mess with my data while i am messing with it and how long will it take to mess with it? If the answer is even the slightest "could be" then you need to think about locking. Either way. You will allways "piss off" one guy.. So you take the pick which one. Also for some applications locking is harder to implement (for example web access with connections that are closed between page calls). See Timestamp fields. and optimistic and pessimistic locking

4. To open a SQL 2005 mdf file you need any Version of SQL 2005. But express is free and can be downloaded. Check the installation instructions for command line installations if you wish to include it in a setup package.

Password Input Mask

In access there is an input mask for a field of type "password". Is there anything similar in SQL Server 2000?No, but you can remove the select permission in the password column in your database|||Access_dude: paradigm shift required...

While Access is a combination database engine and application interface, SQL Server does not have a built-in application interface. Masks, and many other interface features of MS Access are not in SQL Server.

blindman

password in a Table

Hi,

I have a "password" field in a Table. How should I define it, that its
output is showed like "******"?

Thanks,
FaezehHi

You can encrypt the contents of the column using several different methods
see http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=30

If you are using .NET then the best thing to use is one off the algorithms
available through the framework.

The displaying of asterisks in a form is a feature of the form and not part
of SQL Server.

John

"F. Afroozi" <afroozi@.isac.rwth-aachen.de> wrote in message
news:327mtaF3jgn1lU1@.news.dfncis.de...
> Hi,
> I have a "password" field in a Table. How should I define it, that its
> output is showed like "******"?
> Thanks,
> Faezeh
>

Password Field in SQL Server

If i would like to have the field to store password(data, ****), what type
of field in sql server?
is it good to select binary?Storing passwords is an inherently insecure practice that would
probably fall foul of a security audit (a good audit anyway). If you
must roll your own security then store a salted, secure hash of the
password. For that BINARY is the obvious choice.
--
David Portas
SQL Server MVP
--|||Implement your own en/decryptring methody in your middle tier to
accomplish this.
HTH, jens Suessmeyer.

Password Field in SQL Server

If i would like to have the field to store password(data, ****), what type
of field in sql server?
is it good to select binary?
Storing passwords is an inherently insecure practice that would
probably fall foul of a security audit (a good audit anyway). If you
must roll your own security then store a salted, secure hash of the
password. For that BINARY is the obvious choice.
David Portas
SQL Server MVP
|||Implement your own en/decryptring methody in your middle tier to
accomplish this.
HTH, jens Suessmeyer.