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 character

Hi,

I used excel to import data to my database, I found out a problem, my program is linked with the database, when the program show data from the database, it has an extra '@.' symbol, In order to remove it, I need to go to the database to press space bar and backspace at the field. How could I use SQL instead of using space bar and backspace?

Thanks
FrenkHi,

I used excel to import data to my database, I found out a problem, my program is linked with the database, when the program show data from the database, it has an extra '@.' symbol, In order to remove it, I need to go to the database to press space bar and backspace at the field. How could I use SQL instead of using space bar and backspace?

Thanks
Frenk

If this is not a display problem, you can use replace() function to clean up the text.

e.g.
select replace(col,'@.',space(0))
from tb|||but the @. can not be seen in the database, and sometime it shows # instead could you please let me know what's happen|||Do those characters occur as the first character?|||no, it occurs at last character

that I normally do, first go to the last character, then press space bar and backspace, the @. or # will be disappeared|||Run this query and see if you see those characters

Select SubString(yourCol,1,len(yourCol)-1) from yourTable|||I can not see those character, the last character of each reocrd is missing.|||Well. Use that query to display in your application
If you want to remove those from table, then

Update yourTable Set yourCol=SubString(yourCol,1,len(yourCol)-1) where SubString(yourCol,1,len(yourCol)-1) in ('@.','#')

extended stored procedures: is there a 256 character limit on INPUT parameters?

Hello,
On SQL Server 2000 (SP3), for extended stored procedures: is there a
256 character limit on INPUT parameters?
And if so, is there a way around that?
Thanks,
BertAFAIK, there is no such limit.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Bert Szoghy" <webmaster@.quadmore.com> schrieb im Newsbeitrag
news:34276ef9.0504241118.4596c291@.posting.google.com...
> Hello,
> On SQL Server 2000 (SP3), for extended stored procedures: is there a
> 256 character limit on INPUT parameters?
> And if so, is there a way around that?
> Thanks,
> Bert|||Here's a quick test script for you:
CREATE PROCEDURE dbo.usp_Char256Test
@.s VARCHAR(257)
AS
PRINT LEN(@.s)
GO
DECLARE @.TempString VARCHAR(257)
SET @.TempString = REPLICATE('X', 257)
PRINT LEN(@.TempString)
EXEC dbo.usp_Char256Test @.TempString
This creates an SP that allows a 257 char VARCHAR to be passed in. It then
prints the Length of the passed in string to verify that all 257 chars were
passed successfully.
The limit you're encountering is probably due to a CHAR(256) or VARCHAR(256)
limit placed on the definition of the parameter in the SP declaration (above
I set the limit to 257, but any value up to 8000 for VARCHAR [4,000 for
NVARCHAR] should work).
"Bert Szoghy" <webmaster@.quadmore.com> wrote in message
news:34276ef9.0504241118.4596c291@.posting.google.com...
> Hello,
> On SQL Server 2000 (SP3), for extended stored procedures: is there a
> 256 character limit on INPUT parameters?
> And if so, is there a way around that?
> Thanks,
> Bert|||Steve Kass <skass@.drew.edu> wrote in message news:<OJxPTgSSFHA.3716@.TK2MSFTNGP14.phx.gbl>..
.
> Bert,
> For some system extended stored procedures, the parameters
> are defined as varchar(256). For those, there is no way around
> unless you rewrite the xp (not a good idea I think). For xp's you
> write yourself, I'm not aware of any limit. Are you having a
> particular problem you need help with?
> Steve Kass
> Drew University
> Bert Szoghy wrote:
>
Hello,
Thank you for your responses.
I have an extended stored proc DLL with C++ code which just looks fine
to me, which it refuses to give me more than 256 characters.
I found the following reference before posting here which made me
suspect it was a SQL Server limitation:
http://groups.google.ca/groups?hl=e...r />
GP15.phx.
gbl%26rnum%3D1
I am using a varchar(8000) in the trigger calling the extended stored
proc.
I'm about to try stepping through the DLL but the code is a bit nasty
and it would be nice if a SQL Server guru would point out a detail to
tweak.
Thanks guys!
Thanks again,
Bertsql

extended stored procedures: is there a 256 character limit on

Bert,
For some system extended stored procedures, the parameters
are defined as varchar(256). For those, there is no way around
unless you rewrite the xp (not a good idea I think). For xp's you
write yourself, I'm not aware of any limit. Are you having a
particular problem you need help with?
Steve Kass
Drew University
Bert Szoghy wrote:

>Hello,
>On SQL Server 2000 (SP3), for extended stored procedures: is there a
>256 character limit on INPUT parameters?
>And if so, is there a way around that?
>Thanks,
>Bert
>Bert,
The thread you posted indicates there is a 256-character limit
for DBLib, not the SQL Server xp architecture. According to John
Kane in that thread:
Anil,
ESP or (Extended Stored Procs) can use the OLE-DB API and if you are using
the DBLib API you are cutting your own code's future as it is most likely
(un-confirmed) that Microsoft will drop, i.e.. remove the DB-Library API
from future SQL Server versions, such as SQL Server 2005 (Yukon).
See SQL Server 2000 BOL titles "Running Stored Procedures (OLE DB)",
"Extended Stored Procedure Architecture", "Programming Extended Stored
Procedures" and "Samples" and specifically the sample app xp-ODBC for more
info.
This is not a SQL Server limitation, but a limitation of the old DBLib
interface
only. Rewriting the xp to use a more modern interface, as John suggested,
is probably what you need to do.
SK
Bert Szoghy wrote:

>Steve Kass <skass@.drew.edu> wrote in message news:<OJxPTgSSFHA.3716@.TK2MSFT
NGP14.phx.gbl>...
>
>Hello,
>Thank you for your responses.
>I have an extended stored proc DLL with C++ code which just looks fine
>to me, which it refuses to give me more than 256 characters.
>I found the following reference before posting here which made me
>suspect it was a SQL Server limitation:
>http://groups.google.ca/groups?hl=e...K2MSFTNGP15.phx
.gbl%26rnum%3D1
>I am using a varchar(8000) in the trigger calling the extended stored
>proc.
>I'm about to try stepping through the DLL but the code is a bit nasty
>and it would be nice if a SQL Server guru would point out a detail to
>tweak.
>Thanks guys!
>Thanks again,
>Bert
>

extended character search

Is there an easy way to perform a SELECT where you have

blah LIKE 'asdf'

but instead of just returning all the asdf's, it also looks for sdf,
sdf, sdf, etc?Right now, this is what I'm doing: replacing each accent letter (a, e,
i, etc) with a string of possible accents. For example

A search for 'GONCALTRONICA' actually searches with:

'G[o][n][c][a]LTR[o][n][i][c][a]'

and returns the correct row with 'GONALTRNICA' in the field. I'm
thinking there has to be a better way to have a case insensitive
search. Maybe an option somewhere?

Thanks|||"PepperellMA" <andy@.pepperell.net> wrote in message
news:1106163716.326409.20730@.c13g2000cwb.googlegro ups.com...
> Right now, this is what I'm doing: replacing each accent letter (a, e,
> i, etc) with a string of possible accents. For example
> A search for 'GONCALTRONICA' actually searches with:
> 'G[o][n][c][a]LTR[o][n][i][c][a]'
> and returns the correct row with 'GONALTRNICA' in the field. I'm
> thinking there has to be a better way to have a case insensitive
> search. Maybe an option somewhere?
> Thanks

You can specify an accent-insensitive collation in your queries:

create table #pep (col1 nvarchar(100))
insert into #pep select 'GONALTRNICA'

-- Returns 0 rows
select * from #pep
where col1 = 'GONCALTRONICA'
-- Returns 1 row
select * from #pep
where col1 = 'GONCALTRONICA' collate SQL_Latin1_General_CP850_CI_AI

Simon|||I am getting the error "Line 7: Incorrect syntax near 'collate'." Maybe
I am using an out of date version of sql server that doesn't support
COLLATE (Microsoft SQL Server 7.00 - 7.00.623) ?

> You can specify an accent-insensitive collation in your queries:
> create table #pep (col1 nvarchar(100))
> insert into #pep select 'GONALTRNICA'
> -- Returns 0 rows
> select * from #pep
> where col1 = 'GONCALTRONICA'
> -- Returns 1 row
> select * from #pep
> where col1 = 'GONCALTRONICA' collate SQL_Latin1_General_CP850_CI_AI
>
> Simon|||"PepperellMA" <andy@.pepperell.net> wrote in message
news:1106165927.837051.321340@.f14g2000cwb.googlegr oups.com...
> I am getting the error "Line 7: Incorrect syntax near 'collate'." Maybe
> I am using an out of date version of sql server that doesn't support
> COLLATE (Microsoft SQL Server 7.00 - 7.00.623) ?

COLLATE is only available in SQL 2000 - please always mention which version
you have (and to be fair, I shouldn't have assumed you had 2000). In SQL 7,
the sort order is fixed at install time, so you would have to rebuild the
master database to change it.

If it's important enough to you, it might be worth rebuilding (but of course
you run the risk of breaking other code), setting up an additional MSSQL
installation for insensitive searches, or upgrading to 2000, otherwise
you're probably stuck with writing code as you've already done. Fulltext
searching is always case and accent sensitive, so unfortunately that's not
an option either.

By the way, your build version indicates you haven't installed any
servicepacks (SP4 is the latest one for SQL 7).

Simon

Expression help needed

I use the following expression to convert zero length character strings to and int value of 0 :

comno_type == "" ? "0" : comno_type

however, some of the data I'm trying to copy contains 1 character long blank strings or " " to be precise. Can I get the above expression to handle both the zero and 1 char strings at the same time. I've tried things like:

comno_type == "", " " ? "0" : comno_type

but this is a syntax error.

Would I need to do separate expressions to handle each type of string? and if so, how do I get them into the same destination column?

Thanks

Try

(comno_type == "" || comno_type == " ") ? "0" : comno_type

Greg.
|||

Cheers , that's done the trick.

can't see the wood for the trees sometimes me, I really shouldv'e been able to work that out, is there a syntax guide for the expressions or is it in with Books Online?

|||

Yes and yes, ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/8b80403f-6d45-4001-8b12-25a933c663a2.htm

or

Integration Services Expression Reference
(http://msdn2.microsoft.com/en-us/library/8b80403f-6d45-4001-8b12-25a933c663a2.aspx)

|||

TRIM(comno_type) == "" ? "0" : comno_type

Exporting to CSV

I'm not sure if this is possible or not, still new to RS. I need to export a file in CSV format but a vendor needs the very first character to be a '~'. I've tried adding that to the first column name without sucess and RS won't allow me to name it with the ~ in the properties box. Any ideas? Thanks

Can you have the ~ be the only thing on the first row? If so you can write it into the "Header" section. If not, you can write a script task to go in and add it to the first line (among many other options I'm sure).