Good Morning Db forum.
I am working on a query that I need to pull all fields that contain 3 alpha characters. for example BCB001, MCR001, CHP001 and so forth.
Is there a SQL alpha wildcard that I could use to pull all records that have the three alpha chars?
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
Hi
for MS SQL 2000
I am searching users for each letter
WHERE users.name LIKE ('A%')
how can i search For users.name not starting by A to Z ? all except letters
thank you
hi ,
can anyone tell me if there exists a function in SQL Server that help me determine if a variable is ALL Alphanumeric data or not by returning a 0 or 1 when condition fails or succeeds .
thank you
Hello:
Our company needs to (restore/migrate) our Alpha Nt4sp4 sql 6.5sp5a databases to an Intel hardware platform. For the time being the new intel server will be runing Nt4sp4 sql 6.5 sp5a. I understand that if we were to upgrade to sql 7 we would be able to restore the databases without any problems. However upgrading to sql 7 is not an option right now. I have tried to restore a backup from the alpha to an intel based machine, and it fails because the processor types are different.
I have already search technet, but found nothing, except upgrade to sql 7.
I am hoping someone out there could point me in the right direction.
Thank inadvance for your help, I appreciate it very much.
Cindy
please please help?
For the last couple of days i've been trying to transfer a SQL6.5 database from an Alpha platform to an Intel one.
At one point i loaded SQL7 on the Intel platform and tried to perform an upgrade (using the wizard), across the network - but it just did not want to play.
Using Veritas Backup Exec, with SQL6.5 on both machines, i've tried doing a backup of the database on the Alpha platform and then restore to the Intel Platform, but this fails stating that the processors are incompatible
Am i trying something that is impossible?
If anyone could point me in the right direction i'd be truely grateful.
Sharpy
Hello,
I am looking for ways to migrate Sqlserver 6.5 from Alpha to Intel plat form. The only method I heard was Database/Object transfer.
Is there any other way?
Thanks,
yi
Hi,
I'm trying to move a database over from an Alpha to an Intel-base PC. The SQL Server database is on an Alpha server. The 2nd machine is an Intel. When I make a backup of the database from Alpha, copy over the file to the 2nd PC (Intel) and then try to restore it from the 2nd PC, it doesn't sure up on the list of restores when I click on 'Add Device'
The only other way I can get this to work successfully is with a Database Object Transfer, which takes too long (several hours) and limits me to being on site. I need to do this every few weeks so I need a better way to transfer the database over ?
Anyone have any experience with SQL Server on Alpha??
Joyce
Hello,
We want to port our Nt application to Alpha system with Fx!32. Is it possible to have SQl Server on this system.
Is there any SQL server available specifically for Alpha system..? Because there are VB and VC compilers available for Alpha systems.
ThanX
__Vijay VM
In SQL I need to be able to take a varchar parameter @Area and convert it to a float.
The input values for @Area I can't control. They can range from 6300 to 6,300 SqFt to 1.2 Acres .
So to convert this value to a float I basically look through the string and remove everything that isn't a number or a period. Then I would convert this value to square feet based on how large the number is.
Code:
DECLARE @k int, @Temp VarChar(25), @SqFt Float
SELECT@Temp = @Area
select @Temp
select @k = patindex('%[^0-9. ]%', @Temp)
while @k> 0
begin
select @Temp = replace(@Temp, substring(@Temp, @k, 1), '')
select @k= patindex('%[^0-9. ]%', @Temp)
end
If @Temp = ''
BEGIN
SET @Temp = '0'
END
SELECT @SqFt = Convert(Float, @Temp)
--Distinguish if it was acres or square feet
If (@SqFt > 750.00)
BEGIN
SET @SqFt = @SqFt
END
ELSE
BEGIN
SET @SqFt = (@SqFt * Convert(Float,43560) )
END
SELECT @SqFt
This works great except for one situation, If @Area is something like 6,300 Sq.Ft. . When I run it through the part that removes all non-numeric items and periods, I end up with 6300 .. . So to get around this I want to find the first letter in the string and then remove everything after it. Then take the result and run it through part that removes everything but the numbers and period.
However I can't find away to get the index of the alpha-numeric character and remove everything after it.
Thanks in advance!
Hi,
How do I do a conditional check for the left most character if it is an alpha or number in a Derived Column Transformation? Something like
ISNUMERIC(newseq) ? "ABC" : "DEF"
Thanks
We have a table with an indesx that is varchar(7). It contains up to 5 numbers and 2 characters to denote batches. IE. 100a
105zz
1c
2
10001w
In an Access gui I need to be able to sort this field numericaly so each number shows up in its correct position. IE.
1c
2
100a
105zz
10001w
In order to accomplish this, I created a seperate field on the table named Num as varchar(5). I would like to create a trigger that updates this field any time a new batch number is entered. This way I can have my queries order by Num when returning the recordsets to the GUI.
Below is the code that I thought would work, but it is giving me an error near the keyword BEGIN after the IF(ISNUMERIC) statement. I can't seem to find an error with this code, but I thought maybe a thousand fresh sets of eyes could. Any help would be greatly appreciated. Also if you know of a better way to accomplish this task, please feel free to post here.
TIA,
Aaron
CREATE TRIGGER [InsertNum] ON [dbo].[IP_Batch_Table_Temp]
FOR INSERT, UPDATE
AS
DECLARE @Num varchar(7)
DECLARE @Num1 CHAR(7)
DECLARE @x INT
SET @Num = (SELECT IP_Batch_Number FROM inserted)
SET @x = 1
WHILE (@x < len(@Num))
BEGIN
IF (ISNUMERIC( SUBSTRING(@Num, @x, 1) ) )
BEGIN
SET @Num1 = @Num1 + SUBSTRING(@Num, @x, 1)
END
SET @x = @x + 1
END
UPDATE IP_BATCH_TABLE_TEMP SET NUMBER = ltrim(rtrim(@Num1)) WHERE IP_BATCH_NUMBER = @Num
If I create an index on a field in SQL Server, what will be the most efficient (fastest) field type to index a field? (This field will be a "Pointer" to a child table that will contain a list of codes, and their description.)
Would a Numeric field be quicker than a VarChar field?
VarChar would make it easier for a Human to decipher the raw records. (For example, if I used a numeric the code would be 42 or 47, while the VarChar could be 'savings' or 'checking'.)
Basically I will have the following "Master" table: FieldType
---------
IDInt
NameVarChar
StatusInt -or- VarChar
Customer_TypeInt -or- VarChar
If Customer_Type is a code that can be looked up in another table, and I index that field, would I want the "Code" to be an Int or VarChar?
SQL: Select *
From Master
Where Customer_Type = <42> or <'savings'>
My Where clause would depend on the field type.
Thank you, Bryan
HI,
Thanks in advance for taking your time to read this post.
I am trying to write a SQL query using MS SQL 2005 that will read the value of a field and tell if it is alpha or numeric. I have tried the following but it does not work:
select field1 from table1 where left(field1,2)='[0-9]'
select field1 from table1 where isnumber(left(field1,2) tried with a =1 at the end and without and =1 at the end
the goal is to read through a field and format it so if a field looks like this 12xxx111xx I can change it to look like 12-xxx-111-xx.
Any help is greatly apprecaited
Hi I am trying to strip out any non-alpha characters from a field.
i.e. Field = ABC"_IT8*$ should return: ABCIT8
I am writing a loop to do this for all values of a field. The script runs, but hangs....please could somebody advise on the code below...:
I run the script but it doesn't seem to finish. Can anybody see any issues with the code:
DECLARE @Index SMALLINT,
@MATCH_Supplier_name varchar(500),
@Counter numeric,
@Max numeric
-- @sqlstring varchar(500)
SET @Counter = 1
SET @Max = (SELECT Max(DTect_Supplier_SRN) FROM SUPPLIER_TABLE_TEST)
WHILE @Counter <@Max
BEGIN
SET @MATCH_Supplier_name = (SELECT Match_Supplier_Name FROM SUPPLIER_TABLE_TEST WHERE @Counter = DTect_Supplier_SRN)
SET @Index = LEN(@MATCH_Supplier_name)
WHILE @Index > = 1
SET @MATCH_Supplier_name = CASE
WHEN SUBSTRING(@MATCH_Supplier_name, @Index, 1) LIKE '[a-zA-Z]' TH EN SUBSTRING(@MATCH_Supplier_name, @Index, 1)
WHEN SUBSTRING(@MATCH_Supplier_name, @Index, 1) LIKE '[0-9]' THEN SUBSTRING(@MATCH_Supplier_name, @Index, 1)
ELSE ''
END + @MATCH_Supplier_name
SET @Index = @Index - 1
--PRINT @MATCH_Supplier_name
SET @Counter = @Counter + 1
END
Hi,
I have a little bit of a problem I cannot seem to figure it out. Is it possible to write a Select statement that contains a WHERE column_name > desired_numeric_value
The tricky part it that the column is of CHAR type and can contain numeric grades ranging from 0-100 or the letter I for Incomplete.
My SQL was working perfect when this column contained only numbers as soon as a record with I was added I get the following error:
Character to numeric conversion error
This report will be used to find students who have failing grades. Thanks for any help!
How to remove non alpha chars from a column ?
I googled the following code
Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin
[code]....
Hello,
I need to strip out all alpha chars and spaces in a given field and return only the numbers.
I've tried =CInt(Fields!Info.Value) and get an unexplained error. If the data was formatted consitantly I could simply do a RTrim or Right, but the number strings are not the same, some have spaces as in phone numbers (1 800 555 1212) or don't have a leading 1. Most instances are correct for my purpose (8005551212).
Any help would be appreciated.
UPDATE: Using the Replace function =Replace(Fields!Info.Value, " ","") gets me almost there. Now I should be able to use a Right, 10 function to return my desired value. Is it possible to combine these two funtions together?
Am I right in saying that the MS Full-text search engine does not support a wildcard at the beginning of the word? Only at the end? E.g. "VB*" works but "*NET" doesn't.
View 2 Replies View RelatedHi,
We are using VB 6.0 as frontend and SQL server 7.0 as backend. We want to store some special charcters like Alpha, Gamma, etc in the database. We tried different ways but could not find the solution.
Could you please help on this.
Thanks,
Arun.
Karikalan writes "We need alpha numeric auto increment code in sql server 2000.
(for eg.: ico1001, ico1002, ico1003,......)
Can any one send the code in MS sql server 2000? plz ..................
bcoz i am beginner in sqlserver 2000"
Hi, I have created a table with the following
docmd.runSQL "Create Table #Test(A int)"
in Access 2000 and cannot find the table on my table list, it's not on my enterprise manager either. My guess is that maybe it has something to do with the prefix #? So I started searching for "Create table #" on google and dbforums and cannot find anything. Can someone tell me what's special about having a #prefix on a table and where can I find more information about it?
Thanks
SHK
What does the ap_ stand for as a prefix to stored procedures?
A DBA tried to teach me something about the prefixes etc.. but I can't recall what it meant.
In this case ap_ may represent "ONE" persons naming convention?
something procedure...
other common ones in our database are dt_ and zz_
SQL 2000
I am testing a query for use in Crystal Reports. It was copied from an existing query with the necessary adjustments. The first part of it works correctly;
SELECT NA.*
into #cl_temp
FROM OLT.dbo.NACBTR NA
WHERE NA.CourseCode in ('RGF00001','RGF00002','RGF00005','RGF00006', 'RGF00038','RGF00039','RGF00040','RGF00041','RGF00042','RGF00043') And
NA.completedDate >= '01/01/2006' and
NOT EXISTS (SELECT * FROM hrdw.dbo.E_View EV WHERE NA.ssn = EV.ssn)
but when I add the second line;
select #cl_temp.*,
ISNULL((select 1 from #cl_temp where #cl_temp.coursecode = 'RGF00001'),0) as fire_yes into #oshasafety_temp
I receive the error message:
The column prefix '#cl_temp' does not match with a table name or alias name used in the query.
Any ideas?
Thanks,
Tom
btw, sql newbie.
I have an Access database, that is in connection with sql server.
On my computer with access2007 i have no problems with viewing tables and stuff.
But on other computers with access2003 it gives an error when i use this query:
INSERT INTO [tbl_sap-staffel] ( ItemCode, CardCode, [Amount-0], [Price-0], [Amount-1], [Price-1], [Amount-2], [Price-2] )
SELECT [qry_sap-spp-0].ItemCode, [qry_sap-spp-0].CardCode, [qry_sap-spp-0].Amount, [qry_sap-spp-0].Price, [qry_sap-spp-1].Amount, [qry_sap-spp-1].Price, [qry_sap-spp-2].Amount, [qry_sap-spp-2].Price
FROM ([qry_sap-spp-0] LEFT JOIN [qry_sap-spp-1] ON ([qry_sap-spp-0].ItemCode = [qry_sap-spp-1].ItemCode) AND ([qry_sap-spp-0].CardCode = [qry_sap-spp-1].CardCode)) LEFT JOIN [qry_sap-spp-2] ON ([qry_sap-spp-1].ItemCode = [qry_sap-spp-2].ItemCode) AND ([qry_sap-spp-1].CardCode = [qry_sap-spp-2].CardCode);
It says:quote:
ODBC call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]
The column prefix 'MS1' does not match with a table name or alias name used in the query.
The column prefix 'MS2' does not match with a table name or alias name used in the query.
And it will give this error 6 times.
I dont know what to do.
Can anybody help me?
Suppose there is a table containing these recodes.country-------CON_CHNCON_JAPJAPCON_CHNWhen I use the following sql:select country, count(*) as num from table group by countrythe normal result will be:country num---------------CON_CHN2CON_JAP 1JAP1However, my desired result is as follows:country num-----------------CON_CHN2CON_JAP 2How can I re-write my SQL? Or any other methods to do that?
View 2 Replies View RelatedHi,I would like to know details about the table name starts with prefixin sql server 2000.Actually i'm working on existing code.The existing code insert a record into a table, but the table name inthe code and table name in database are differentTable name in database : tbl_mmm_oxIn coding they are using table name as mmm only, the records areproperly inserted into mmm tableis it possible?The sample code is like that(using ado object)oCmd.CommandText = "INSERT INTO mmm (no,name) values (1,"mm")"The above code is perfectly working and inserting record intotbl_mmm_ox.Could anybody explain how is it possible?whether we can leave the prefix(tbl) and suffix(ox) and sql servertake care of this?Thanks & Regards,Mani
View 7 Replies View RelatedI read a previous post that Raul responded to on the format of the data prefixing Symmetric key encrypted columns, is it possible to reproduce these from a client? Given I know the key name, i can pull back the first 16 for the GUID, for now 01000000 will work for the version, the IV can be created or read, but the last 8 bytes were marked as 'header', can I get an explanation on what this is or if it is required?
The purpose is in being able to do SQL Server compatible encryption on the client side, given a shared certificate for the public password. I certainly can do this with a CLR function and use my own encryption, but comparably it is dog slow, the built in SQL functions will encrypt/decrypt 100k rows in about 20 seconds or less on my test box, where as the CLR function takes 5.5 minutes. This performance difference is too huge to ignore.
I would like to be able to generate a SQL Server compatible prefix for a Symmetric keyed column or find a way to improve the CLR function performance of an AES_256 (rijndael) up to something at least remotely close to the built in functions.
Any help would be appreciated.
John Gooding
Hi forum
I need some advice regards setting a serial prefix to PK ID colomn. Currently it starts at row 1 with increased increments of 1. Thats all good! but I would like is to add a prefix before the 1, ie W2W01/1 next W2W01/2 an so on. Many thanks Paul
I get the error below from the following SQL. This SQL worked until I tried adding a third table "ship_to_salesrep". If I comment-out the third join and the last condition it works.
I have tried putting the "r" table in the FROM statement and still it does not work. Everything looks right to me -- what am I doing wrong?
Server: Msg 107, Level 16, State 2, Line 2
The column prefix 'h' does not match with a table name or alias name used in the query.
SELECT distinct
m.inv_mast_uid,
CONVERT(DECIMAL(10,4),0.00) as 'inv_cost',
CONVERT(DECIMAL(10,4),0.00) as 'oe_cost',
l.invoice_line_type, s.cost,
h.invoice_no, h.order_no, h.order_date, h.invoice_date, h.customer_id,
h.ship_to_id, h.ship2_name, h.ship2_address2, h.ship2_city, h.ship2_state, h.ship2_postal_code,
h.terms_desc, h.po_no, h.salesrep_id, h.salesrep_name, h.period, h.year_for_period,
h.ship_date, h.total_amount, h.amount_paid, h.terms_taken, h.allowed, h.paid_in_full_flag,
h.last_maintained_by, h.printed, h.printed_date, h.shipping_cost, h.invoice_reference_no,
h.invoice_adjustment_type, h.memo_amount, h.bad_debt_amount, h.invoice_class, h.period_fully_paid,
h.year_fully_paid, h.approved, h.other_charge_amount, h.tax_amount, h.original_document_type,
h.date_paid, h.print_flag, h.print_date, h.customer_id_number, h.date_created, h.date_last_modified,
h.consolidated, h.sold_to_ah_uid, h.sold_to_customer_id, h.invoice_batch_uid, h.sales_location_id,
h.source_type_cd,
l.qty_requested, l.qty_shipped, l.unit_of_measure, l.item_id, l.item_desc, l.unit_price,
l.extended_price, l.gl_revenue_account_no, l.gl_salse_tax_account_no, l.pricing_quantity, l.line_no,
l.sales_cost, l.commission_cost, l.other_cost, l.other_charge_item, l.exceptional_sales, l.pricing_unit,
l.invoice_line_uid, l.invoice_line_uid_parent
into jch1.dbo.sales_history_invoices
FROM
invoice_hdr h, invoice_line l
left join inv_mast m on l.item_id = m.item_id
left join inventory_supplier s on m.inv_mast_uid = s.inv_mast_uid
left join ship_to_salesrep r on h.ship_to_id = r.ship_to_id
WHERE
l.invoice_no = h.invoice_no and
h.invoice_date >= '2006-07-01' and
h.invoice_date < '2006-08-01' and
l.invoice_line_type = 0 and
m.inv_mast_uid is not NULL and
r.primary_salesrep_flag = 'Y';
Thanks,
Charles
hey all,
I've posted about this before so apologies to anyone who read it the first time.
I want to have an ID column in my table, but I want the automatically generated number to have a prefix such as ABC. I'd also like to be able to change this prefix from an asp page depending on what is selected from a drop down.
Is any of this possible? I'd really appreciate a reply, even if it's to say this can't be done. Thanks
I have one table called tblCustomer.I have done a SQL select query
Select SalesDate, OrderNo
From tblCustomer
Table 1 is result of SQL
OrderID is Primary Key
There are two fields I want to convert to number starting with prefix C on each row then followed by combining SalesDate + OrderNo
SalesDate OrderNo
01/1/2014 0678
12/1/2014 9099
15/1/2014 8745
I need to covert this two field into one field which I want it to start with Prefix C AS SalesOrdeNumber column.Find below example what I want after completing this exexercse.Table 2 third column (SalesOrdeNumber) is what I need as result of Concatenation (SalesDate+ OrderNo +C)
SalesDate OrderNoSalesOrdeNumber
2014/01/20 0678 C 1401200678
2013/09/259099 C 1309259099
2013/04/308745 C 1304308745
SalesOrderNumber is made by combining SalesDate +OrderNo Fields (yymmdd+OrderNo+C (as prefix on every row))
Point to Note
Date Format on SalesDate Field YYYY-MM-DD
OrderNo field is maximum digit is 4
Am just on the lookout to find out whether there is a way to put a prefix at the beginning of the transaction log backup file that is created by SQL Server logshipping.
So, instead of having <DatabaseName>_<DateTime>.BAK (which is what SQL does), I would like to have TL_<DatabaseName>_<DateTime>.BAK.
I have looked into a parameter for the sqllogship.exe command, but cannot find any & have looked at fields in the logshipping tables, & can't see anything there either.