Extract lower case characters

I need to split a string in two if there are lowercase characters at
the end of it.

For example:
'AAPLpr' becomes 'AAPL' and 'pr'
'Ta' becomes 'T' and 'a'
'MSFT' becomes 'MSFT' and ''
'TAPA' becomes 'TAPA' and ''

I am using SQL 2000. I read about "collate Latin1_General_CS_AS" but
not sure if I can use that outside of a select statement.

Thank you in advance for any help.You can specify a case-sensitive collation either on the column definition:

CREATE TABLE SomeTable (x VARCHAR(6) COLLATE Latin1_General_CS_AS PRIMARY
KEY)

INSERT INTO SomeTable VALUES ('AAPLpr')
INSERT INTO SomeTable VALUES ('Ta')
INSERT INTO SomeTable VALUES ('MSFT')
INSERT INTO SomeTable VALUES ('TAPA')
INSERT INTO SomeTable VALUES ('AbCdE')

SELECT
LEFT(x,PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%',x+'a')-1),
SUBSTRING(x,PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%',x+'a'),LEN(x))
FROM SomeTable

Or, as part of a string expression:

SELECT
LEFT(x, PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%'
COLLATE Latin1_General_CS_AS,x+'a')-1),
SUBSTRING(x, PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%'
COLLATE Latin1_General_CS_AS,x+'a'),LEN(x))
FROM SomeTable

Result:

-- --
AAPL pr
A bCdE
MSFT
T a
TAPA

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<xfKdneMR5PHz97zdRVn-hw@.giganews.com>...
> You can specify a case-sensitive collation either on the column definition:
> CREATE TABLE SomeTable (x VARCHAR(6) COLLATE Latin1_General_CS_AS PRIMARY
> KEY)
> INSERT INTO SomeTable VALUES ('AAPLpr')
> INSERT INTO SomeTable VALUES ('Ta')
> INSERT INTO SomeTable VALUES ('MSFT')
> INSERT INTO SomeTable VALUES ('TAPA')
> INSERT INTO SomeTable VALUES ('AbCdE')
> SELECT
> LEFT(x,PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%',x+'a')-1),
> SUBSTRING(x,PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%',x+'a'),LEN(x))
> FROM SomeTable
> Or, as part of a string expression:
> SELECT
> LEFT(x, PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%'
> COLLATE Latin1_General_CS_AS,x+'a')-1),
> SUBSTRING(x, PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%'
> COLLATE Latin1_General_CS_AS,x+'a'),LEN(x))
> FROM SomeTable
> Result:
> -- --
> AAPL pr
> A bCdE
> MSFT
> T a
> TAPA

Wonderful. Thank you for your help.sql

Extract a character string from a text field

I need to extract a character string from a text field. The string I'm
looking for will always start with the first four characters of "ABC-" and
then will end with three numbers (0-9) in varying combinations, ex.
"ABC-508". The problem is that the position of the string in the text field
is different in each record and the last three characters of the string will
vary as described above. Any help is greatly appreciated
--
Finn GirlOne method:
SELECT
CASE
WHEN PATINDEX('%ABC-[0-9][0-9][0-9]%', MyTextCol) = 0 THEN
NULL
ELSE
SUBSTRING(MyTextCol, PATINDEX('%ABC-[0-9][0-9][0-9]%',
MyTextCol), 7)
END AS ExtractedValue
FROM dbo.MyTable
You can encapsulate the code in a proc or function for reusability.
Hope this helps.
Dan Guzman
SQL Server MVP
"FinnGirl" <FinnGirl@.discussions.microsoft.com> wrote in message
news:576F175D-855C-424F-A8D7-80E4A5B110CE@.microsoft.com...
>I need to extract a character string from a text field. The string I'm
> looking for will always start with the first four characters of "ABC-" and
> then will end with three numbers (0-9) in varying combinations, ex.
> "ABC-508". The problem is that the position of the string in the text
> field
> is different in each record and the last three characters of the string
> will
> vary as described above. Any help is greatly appreciated
> --
> Finn Girl|||create table #foo (SomeColumn varchar(50))
insert into #foo values ('test ABC-508')
insert into #foo values ('testing ABC-509')
insert into #foo values ('ABC-123')
insert into #foo values ('FinnGirlABC-524')
insert into #foo values ('abcABC-456')
select * from #foo
--this shows you the charindex
SELECT CHARINDEX('ABC-', SomeColumn) FROM #foo
--this gets the desired information:
SELECT SUBSTRING(SomeColumn, (CHARINDEX('ABC-', SomeColumn)), 7) FROM #foo
drop table #foo
Keith Kratochvil
"FinnGirl" <FinnGirl@.discussions.microsoft.com> wrote in message
news:576F175D-855C-424F-A8D7-80E4A5B110CE@.microsoft.com...
>I need to extract a character string from a text field. The string I'm
> looking for will always start with the first four characters of "ABC-" and
> then will end with three numbers (0-9) in varying combinations, ex.
> "ABC-508". The problem is that the position of the string in the text
> field
> is different in each record and the last three characters of the string
> will
> vary as described above. Any help is greatly appreciated
> --
> Finn Girl

Extra Characters appended to entry

I'm encountering a strange problem in all the applications I'm working on and am totally dumbfounded as to why it's occuring:
From a standard web form I'm inserting a record using a stored procedure. (I'm writing this to a SQL 2000 db - where the column types and variables are all consistant) No matter what I do, the columns are padded with extra characters maxing out the field length (if it's nchar or nvarchar or char) after insert or update. I've tried Trim - ing the field.text values that I'm feeding to the @.variables used in my stored procedure. I've even RTRIM() - ed the @.variables within the stored procedure. No matter what I do I get extra spaces padding the end of the intended column input. Ideas anyone?
Thanks in advance.
- AbeDepending on your extra characters, trimmimg won't solve your problem if they are not whitespaces.
Could you supply more info and post your ASP and T-Sql code?
|||

Thanks. I'm pretty sure it's spaces because if I run a query "SELECT Education FROM tblCV WHERE (Education LIKE N'% %')" I get all records returned except the ones that I manually stripped out the trailing spaces.

privatevoid InsertCV()
{

SqlConnection conn =new SqlConnection(connectString);

SqlCommand cmd =new SqlCommand("sp_InsertCV",conn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@.AAASS_ID",SqlDbType.NVarChar,5);

cmd.Parameters.Add("@.Education",SqlDbType.NChar,700);

cmd.Parameters.Add("@.Experience",SqlDbType.NChar,700);

cmd.Parameters.Add("@.Publication",SqlDbType.NChar,700);

cmd.Parameters.Add("@.ConferencePapers",SqlDbType.NChar,700);

cmd.Parameters.Add("@.OrganizationalMembership",SqlDbType.NChar,700);

cmd.Parameters.Add("@.EnteredBy",SqlDbType.NVarChar,10);

cmd.Parameters.Add("@.UpdatedBy",SqlDbType.NVarChar,10);

cmd.Parameters["@.AAASS_ID"].Value = _AAASSID;

cmd.Parameters["@.Education"].Value =this.education.Text;

cmd.Parameters["@.Experience"].Value =this.experience.Text;

cmd.Parameters["@.Publication"].Value =this.publications.Text;

cmd.Parameters["@.ConferencePapers"].Value =this.conferencePapers.Text;

cmd.Parameters["@.OrganizationalMembership"].Value =this.organizationalMembership.Text;

cmd.Parameters["@.EnteredBy"].Value = _AAASSID;

cmd.Parameters["@.UpdatedBy"].Value = _AAASSID;

conn.Open();

cmd.ExecuteNonQuery();

cmd.Dispose();

conn.Close();

}
CREATE PROCEDURE sp_InsertCV
(
@.AAASS_ID AS nvarchar(5),
@.Education AS nchar(700),
@.Experience AS nchar(700),
@.Publication AS nchar(700),
@.ConferencePapers AS nchar(700),
@.OrganizationalMembership AS nchar(700),
@.EnteredBy AS char(10),
@.UpdatedBy AS char(10)
)

AS
INSERT INTO [AAASSSQLData].[dbo].[tblCV]
(
[AAASS_ID], [Education], [Experience], [Publication], [ConferencePapers], [OrganizationalMembership], [DateEntered], [EnteredBy],
[DateUpdated], [UpdatedBy]
)
VALUES(
@.AAASS_ID,RTRIM(@.Education),RTRIM(@.Experience),
RTRIM(@.Publication),
RTRIM(@.ConferencePapers),
RTRIM(@.OrganizationalMembership),
GetDate(),
RTRIM(@.EnteredBy),
GetDate(),
RTRIM(@.UpdatedBy)
)
GO
tblCV
3 AAASS_ID nvarchar 5 0
0 Education nchar 700 1
0 Experience nchar 700 1
0 Publication nchar 700 1
0 ConferencePapers nchar 700 1
0 OrganizationalMembership nchar 700 1
0 DateEntered datetime 8 1
0 EnteredBy char 10 1
0 DateUpdated datetime 8 1
0 UpdatedBy char 10 1
Column contents: "SUNY Binghamton, B.A., Psychology, 1982
Pace U., MBA, Management Information Systems, 1986 "
Thank you for taking a look at this.
- Abe

|||Use varchar or nvarchar fields and they won't be padded. Char and nchar datatypes pad the data with spaces out to the field length.|||Thanks so much for the feedback. I'll give that a try.
Best,
- AbeR

Extended Stored Procedure Overhead ?

Hi,
We have a "Clean Name" function that I wrote in T-SQL that strips
off unwanted characters and does a lot of other logic on a customer
name in order to get the best possible match. This function was
running fine, but we now have to run it on a lot more data and it was
taking about 36hrs to run. Because we are still on 2000 we decided to
write an extended stored procedure with the function written in C
thinking it would be a lot faster. Well now that the function is
complete and tested we are doing speed tests and the T-SQL one is
actually faster!
The CPU load when running the T-SQL one is about 30% but when I run
the C one it uses less then 5% of the CPU. I was wondering why the
extended stored procedure one would be so slow ?On Jan 11, 8:45 am, isme...@.gmail.com wrote:
> Hi,
> We have a "Clean Name" function that I wrote in T-SQL that strips
> off unwanted characters and does a lot of other logic on a customer
> name in order to get the best possible match. This function was
> running fine, but we now have to run it on a lot more data and it was
> taking about 36hrs to run. Because we are still on 2000 we decided to
> write an extended stored procedure with the function written in C
> thinking it would be a lot faster. Well now that the function is
> complete and tested we are doing speed tests and the T-SQL one is
> actually faster!
> The CPU load when running the T-SQL one is about 30% but when I run
> the C one it uses less then 5% of the CPU. I was wondering why the
> extended stored procedure one would be so slow ?
I think I'll answer this one my self.
When I was doing my tests I was doing it on a VMWare server, but once
we moved it over to a real server, we got about 4x the performance!
My guess is that all the context switches kill the VM.|||> I think I'll answer this one my self.
> When I was doing my tests I was doing it on a VMWare server, but once
> we moved it over to a real server, we got about 4x the performance!
> My guess is that all the context switches kill the VM.
Yes, performance testing needs to be done on a real server, not a virtual
one.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<ismell1@.gmail.com> wrote in message
news:19e824b8-db7f-467f-a2de-aaf98d9d7f48@.i29g2000prf.googlegroups.com...
> On Jan 11, 8:45 am, isme...@.gmail.com wrote:
>> Hi,
>> We have a "Clean Name" function that I wrote in T-SQL that strips
>> off unwanted characters and does a lot of other logic on a customer
>> name in order to get the best possible match. This function was
>> running fine, but we now have to run it on a lot more data and it was
>> taking about 36hrs to run. Because we are still on 2000 we decided to
>> write an extended stored procedure with the function written in C
>> thinking it would be a lot faster. Well now that the function is
>> complete and tested we are doing speed tests and the T-SQL one is
>> actually faster!
>> The CPU load when running the T-SQL one is about 30% but when I run
>> the C one it uses less then 5% of the CPU. I was wondering why the
>> extended stored procedure one would be so slow ?
> I think I'll answer this one my self.
> When I was doing my tests I was doing it on a VMWare server, but once
> we moved it over to a real server, we got about 4x the performance!
> My guess is that all the context switches kill the VM.

Extended Stored Procedure Overhead ?

Hi,
We have a "Clean Name" function that I wrote in T-SQL that strips
off unwanted characters and does a lot of other logic on a customer
name in order to get the best possible match. This function was
running fine, but we now have to run it on a lot more data and it was
taking about 36hrs to run. Because we are still on 2000 we decided to
write an extended stored procedure with the function written in C
thinking it would be a lot faster. Well now that the function is
complete and tested we are doing speed tests and the T-SQL one is
actually faster!
The CPU load when running the T-SQL one is about 30% but when I run
the C one it uses less then 5% of the CPU. I was wondering why the
extended stored procedure one would be so slow ?
On Jan 11, 8:45 am, isme...@.gmail.com wrote:
> Hi,
> We have a "Clean Name" function that I wrote in T-SQL that strips
> off unwanted characters and does a lot of other logic on a customer
> name in order to get the best possible match. This function was
> running fine, but we now have to run it on a lot more data and it was
> taking about 36hrs to run. Because we are still on 2000 we decided to
> write an extended stored procedure with the function written in C
> thinking it would be a lot faster. Well now that the function is
> complete and tested we are doing speed tests and the T-SQL one is
> actually faster!
> The CPU load when running the T-SQL one is about 30% but when I run
> the C one it uses less then 5% of the CPU. I was wondering why the
> extended stored procedure one would be so slow ?
I think I'll answer this one my self.
When I was doing my tests I was doing it on a VMWare server, but once
we moved it over to a real server, we got about 4x the performance!
My guess is that all the context switches kill the VM.
|||> I think I'll answer this one my self.
> When I was doing my tests I was doing it on a VMWare server, but once
> we moved it over to a real server, we got about 4x the performance!
> My guess is that all the context switches kill the VM.
Yes, performance testing needs to be done on a real server, not a virtual
one.
Hope this helps.
Dan Guzman
SQL Server MVP
<ismell1@.gmail.com> wrote in message
news:19e824b8-db7f-467f-a2de-aaf98d9d7f48@.i29g2000prf.googlegroups.com...
> On Jan 11, 8:45 am, isme...@.gmail.com wrote:
> I think I'll answer this one my self.
> When I was doing my tests I was doing it on a VMWare server, but once
> we moved it over to a real server, we got about 4x the performance!
> My guess is that all the context switches kill the VM.

Exporting To PDF With Chinese/Jap Characters

Hi all!I have created a report that may display Chinese, Korean and Japanese characters. Everything is fine on the report itself when rendered in the browser, all the characters are showing as it should be, but when I try to export it to pdf, all these characters turned into question marks ??.

I've tried installing Adobe Asian pack, but still I can't make these character shows up.Can anyone shed light on this? I'm doing a local report only BTW. Would there be any effect if I'll do it as a server report?

Anything that could get me started would be greatly appreciated.Thanks in advance.

I've had these kind of issues to when I used what is supposed to be a unicode font, Arial. After switching to a different unicode font, MS Gothic, everything printed correctly. Try that.

Eric

|||:O that's weird, but I'll try it tomorrow in the office|||

I've tried MS Gothic, the chinese character showed correctly but for Jap, Korean and Thai it didn't :( So tried other fonts and the closest that can show Chinese, Jap & Korean is the font Batang, though it cannot display Thai :(

I've googled endlessly post on different forums but still got any clear answer as to if this is a limitation on SSRS or what.

|||

Sorry I can't help you any more. I'm still pretty new to unicode fonts.

Eri

|||

I'm having the same exact issue. I tried using Batang as a font but it still will not show up properly in pdf. have you found a fix for this?

|||

No prob Eric. Actually you already helped me when you said try using other fonts. It will be my temporary fix for the mean time.

@.user

I dunno if the Asian Language pack from adobe would help. I have installed it before I tried changing the font style on the report.