How To Find Alphanumeric Values In Name
Mar 26, 2015
I have a column called firstname ..in that it stores value like this
john smith
andrew jr
jim sr
andrew bar
tina *^
don $%
I need to retrieve all those rows where name consists of non alphabets...for example 5 and 6 has non alphabets..
I am using PATINDEX('%[^a-z]%',Firstname) function but if it finds space between names it is considering as error..I would like to find only non alphabets in name ..space is fine..is there any function to find out?
View 2 Replies
ADVERTISEMENT
Jun 12, 2015
create table example (f varchar(10))
insert into example values('fd')
insert into example values('fd')
insert into example values('fd1')
insert into example values('fd23')
insert into example values('fda23')
insert into example values('fd23g')
output:-
[A-Z][A-Z]
[A-Z][A-Z]
[A-Z][A-Z][0-9]
[A-Z][A-Z][0-9][0-9]
[A-Z][A-Z][A-Z][0-9][0-9]
[A-Z][A-Z][0-9][0-9][A-Z]
How to write SQl query to fetch this type of output.
View 6 Replies
View Related
May 11, 2015
Trying to use LIKE / NOT LIKE to identify values that contain any alphanumeric characters outside of A-Z e.g £%$^&*_-{[@ etc etc
The field should contain only values between A-G with a numberic e.g ABCD1234567... but some rows have characters such as above, some have spaces (weeps) , and some have letters outside the A-G range ....
View 7 Replies
View Related
Apr 2, 2008
Hi,
I am using SQL Server 2000. In database i am having one column named Address which contains full address of the customer. While searching i want to ignore starting numeric or alphanumeric values. Kinly guide how I can ignore numeric or alphanumeric values while searching the data.
View 8 Replies
View Related
Dec 18, 2007
Is it possible to write a sql statement to skip aplpha numeric values? I got a field containing these values; 20, 70, 150, 140, 100, KORT, 90, 180, 160. And I'm trying to check if any value is bigger than 175 (@Limit), but I want to skip the value 'KORT'. So is it possible to check if a value is numeric or not? ISNULL( CONVERT(int, ProductVariant.Size), 0) > @Limit Regards, Sigurd
View 2 Replies
View Related
May 8, 2007
Hi,
I want to search for alphanumeric values between an upper and lower bound in a sql database.For example: search for a serial number like pvf-456-3b. Upper bound is q, lower bound is g.I should then get every serial number starting with g - q.If possible the bounds should be more specific like "search for serial number between gt2 and qy"Can anybody help me out?
View 7 Replies
View Related
Nov 6, 2007
Hi All ,
I am having a table which contain data in below form
id startvalue endvalue
1 12:00a 12:29a
2 12:30a 12:59a
3 1:00a 1:29a
4 1:00p 1:29p
5 2:00a 2:29a
Through SQL query I want to select the id column value for the row returned for the following query
select id from mytable where '1:24a' >= startvalue and '1:24a' <= endvalue
Idea is I will pass a literal value that lies between the startvalue (nvarchar column) and endvalue (nvarchar column) to fetch the id of the row returned, which should be 3 But when I run the query it returns me 2 rows for id 3 and 4.
I believe this is because I am trying to compare nvarchar columns with ">=" and "<=" operators.
Can anyone suggest how i can select the correct value or how to do this.
Thanks,
Ashish
View 2 Replies
View Related
Feb 11, 2015
For a new project. I need to create random alphanumeric characters as primary key values when inserting a record.
eg: cmSbXsFE3l8
It can start from 4 digit characters and can grow to 6, 7 as required
The database will involve more than 10000 concurrent users.
I don't want to use guid or auto increment integer or sequence.
View 9 Replies
View Related
May 30, 2014
I am working with SP. How can we find out values of parameters when the SP is executed with the default values?
View 9 Replies
View Related
Mar 9, 2008
Hi I want to find 3 different columns maximum values in one shot. Like I tried to use a reader to go through results but it kept coming back with index out of bounds. Right now to get it to work I got to use a ExecuteScalar() get the first columns max value and then open the connection again(since it seems after ExecuteScalar() it closes the connection) and then do the ExecuteScalar() again. Open the connection again and do the ExecuteScalar() again. Theres got to be a better way of doing this.
View 3 Replies
View Related
May 28, 2008
I have written this script: What I want to do now is, with the new JOINED table find and display all the duplicates custID WHERE the price is either 100 OR 200. Can anyone help? I am very new to all this and can't see how to do it. Thanks!
SELECT
*
FROM
table_customer T1
INNER JOIN
table_itemsBought T2
ON
T2.custID = T1.custID
WHERE
price = '100'
OR price = '200';
View 2 Replies
View Related
Dec 7, 2007
I was wondering how to do a find and replace with SQL? Would I use a SET statement?
I need to find a specific value and replace all of the results with a different value.
Thanks for all responses.
View 1 Replies
View Related
Apr 11, 2008
I am trying to merge records with a stored procedure and I am getting a constraint violation error. Just wondering is there a way of finding out which records are the actual culprit?
This would help greatly in being able to troubleshoot this issue.
The error is this:
2/21/2008 12:40:39 PM :: Violation of UNIQUE KEY constraint 'SK_SomeConstraint'. Cannot insert duplicate key in object 'tblTable'.
The statement has been terminated.
The constraint is a group of clustered indexes that must be unique:
GO
/****** Object: Index [SK_SomeConstraint] Script Date: 04/11/2008 14:56:17 ******/
ALTER TABLE [dbo].[tblTable] ADD CONSTRAINT [SK_SomeConstraints] UNIQUE NONCLUSTERED
(
[IDa] ASC,
[IDb] ASC,
[IDc] ASC,
[someField] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
View 4 Replies
View Related
Dec 10, 2013
Is there a way to find duplicate values and get the timediff from the start and end of each duplicate.
datetimeTagname value MachineValueTime Diff Minutes
12/9/13 8:55machine_1 14,423 Machine_1 14,423 5
12/9/13 9:00machine_1 14,423 Machine_1 14,428 9
12/9/13 9:05machine_1 14,428 Machine_1 42,743 4
12/9/13 9:10machine_1 14,428
12/9/13 9:14machine_1 14,428
12/9/13 11:32machine_1 42,743
12/9/13 11:36machine_1 42,743
View 6 Replies
View Related
Feb 14, 2008
Hello,
I forget. How can PATINDEX be used to find column values where the data is all numeric? I've tried, the following:
select distinct acct from tbl_CYProcessedSales
where PatIndex('%[0-9]%',Acct) > 0
order by acct
Acct is varchar(8). What am I doing wrong?
Thank you for your help!
CSDunn
View 12 Replies
View Related
Mar 25, 2008
I have a field which is currently of the "date time" data type. i want to convert it to smalldatetime, but everytime I try, i get an error of the "The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error. " sort.
I have tried to find the values which are out of the smalldatetime range, with the following query
SELECT *
FROM midmar
WHERE bday BETWEEN '01/01/1900' AND '06/05/2079'
Which doesn't quite work, and gives me values that are actually between those two values listed.
I have also tried having the WHERE clause read:
WHERE bday <'06/06/2079' AND
bday>'01/01/1900'
and that doesn't really work either.
What's going on? Is there likely another problem besides the structuring of my queries?
View 9 Replies
View Related
Jan 10, 2014
I have a program which feeds back information to a table within my database. The annoying thing is that only 1 of the table cells has all of the information I need so essentially I have a large string of data I need to retrieve values from.I retrieve my cell with a simple select details from alerts where ID = (alert number).The results I have are as follows:
[INFO] Dashboard Output Information:
[LastUpdated]
Time=09:59
[LastProcessed]
LastProcessed=*2911
Updated=09:59
[LastReceived]
LastReceived=#10
Updated=09:59
As you can see I have a list (its larger but this is a sample), the only thing I need are the values for each section so for last processed i want the 2911 so I can use it on my dashboard and the last received I want the 10 for that section.i have read about using substrings but am really not sure where to start and how I'd go about getting these values to then use elsewhere?
View 3 Replies
View Related
Jul 20, 2005
Help, please. I am trying to update atable with this structre:CREATE TABLE Queue (PropID int, EffDate smalldatetime,TxnAmt int)INSERT Queue (PropID) SELECT 1INSERT Queue (PropID) SELECT 2INSERT Queue (PropID) SELECT 3....from this table...CREATE TABLE Txns (PropID int, TxnDate smalldatetime,TxnType char(1), TxnAmt int)INSERT Txns SELECT 1 '20000201', 'B', 100000INSERT Txns SELECT 1 '20020515', 'B', 110000INSERT Txns SELECT 1 '20020515', 'A', 120000INSERT Txns SELECT 1 '20020615', 'c', 130000....only certain txn types are okay, and they have an orderof preference...CREATE TABLE GoodTxnTypes (GoodTxnType char(1), Pref)INSERT GoodTxnTypes SELECT 'A', 1INSERT GoodTxnTypes SELECT 'B', 2The idea is to fill in the NULL fields in the Queue table,according to a rule -- the transaction must be the latesttransaction within a date window, it must be one of the goodtxn types, and if there are two txns on that date, choosethe txn by the preferred txn type (A is preferred over B,according to the field Pref).If the time window were 20020101 to 20030101, the txnselected to update the Queue table would be this one:INSERT Txns SELECT 1 '20020515', 'A', 120000 -- there aretwo in the time window that are type A or B; they areboth on the same day, so the 'A' is preferred.If the time window were 20000101 to 20010101, this wouldbe selected because it is the only A or B type txn inthe interval:INSERT Txns SELECT 1 '20000201', 'B', 100000I'm looking for a statement that starts...UPDATE Queue SET EffDate = ...., TxnAmt = .... (EffDate,in this table, is the same as TxnDate in the Txn table).Assume we have @FirstDate and @LastDate available.Help, please. I'm getting stuck with (a) a sub-query tofind the relevant Txn records, and (b) another sub-querywithin that to find the MAX(TxnDate) within the timewindow. Filtering the Txn records on the basis of theGoodTxnTypes table is easy, as is ordering what is returned.But I'm having trouble joining the sub-queries back to theQueue table on the basis of PropId.
View 1 Replies
View Related
Feb 13, 2007
Hi
I have a really simple query which i can't figure out why its not working. I have a table called 'ADMIN' which has a datetime field called 'date_edited'. Because the majority of records have never been edited, i have allowed null values and they are filled with 'NULL' in each record. How ever, when i try:
SELECT * FROM ADMIN WHERE date_edited = NULL
I get no records, but i can see and know i have hundreds! I know i'm doing somthing really stupid, but for life of me can't figure it out! :eek:
thanks
View 10 Replies
View Related
Mar 21, 2014
I have a table with data in two columns, item and system. I am trying to accomplish is we want to compare if an item exists in 1, 2 or all systems. If it exists, show item under that system's column, and display NULL in the other columns.
I have aSQL Fiddle that will allow you to visualize the schema.
The closest I've come to solving this is a SQL pivot, however, this is dependent on me knowing the name of the items, which I won't in a real life scenario.
select [system 1], [system 2], [system 3]
from
(
SELECT distinct system, item FROM test
where item = 'item 1'
) x
pivot
(
max(item)
[Code]...
View 2 Replies
View Related
Sep 20, 2015
I have basic knowledge of T-SQL and I am using Cursors to get the first value, the last value and the peak value and some other values from other tables. I found some examples on google but the code I am using is mixed up. I am using multiple Cursors. I need to join three tables to get the result set into the Cursor. The first example uses 2 tables.
@FirstName NVARCHAR,
@LastName NVARCHAR,
@FirstValue decimal,
@HighestValue decimal,
@LastValue decimal
-- First Cursor
DECLARE TESTCURSOR CURSOR
DYNAMIC
[Code] ....
The above code seems totally inefficient but it gives the correct result. Now I want to pull some more value and join a third table (TABLE z) in the above CURSORS and not sure how to make it working using CURSORS.I would like to use the following in the CURSORS above.
SELECT x.publishdate, y.firstname, y.lastname, y.age, z.initialValue AS FirstValue, z.HighestValue AS Highest, z.LastValue AS Last
FROM TABLE x
LEFT OUTER JOIN TABLE y
ON x.id = y.id
INNER JOIN TABLE z
ON x.id = z.id
View 9 Replies
View Related
Mar 23, 2006
Hi All,
I developed a report with some values in textboxes. I want the output not to wrap around to the next line but to be truncated if it is more that the size of the textbox. Is there any setting that i can do b/c values are going to the second line when their size is more like printing name and last name will cause last name to go to the second line ?
Thank you in advance.
View 5 Replies
View Related
Dec 12, 2013
I have my sql tables and query as shown below :
CREATE TABLE #ABC([Year] INT, [Month] INT, Stores INT);
CREATE TABLE #DEF([Year] INT, [Month] INT, SalesStores INT);
CREATE TABLE #GHI([Year] INT, [Month] INT, Products INT);
INSERT #ABC VALUES (2013,1,1);
INSERT #ABC VALUES (2013,1,2);
[code]....
I have @Year and @Month as parameters , both integers , example @Year = '2013' , @Month = '11'
SELECT T.[Year],
T.[Month]
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
,
(SELECT SUM(Stores)
FROM #ABC
WHERE [Year] = T.[Year]
AND [Month] = T.[Month]) AS [Sum_Stores],
(SELECT SUM(SalesStores)
[code]....
What I want to do is to add more columns to the query which show the difference from the last month. as shown below. Example : The Diff beside the Sum_Stores shows the difference in the Sum_Stores from last month to this month.
Something like this :
+------+-------+------------+-----------------+-----|-----|---+-----------------
| Year | Month | Sum_Stores |Diff | Sum_SalesStores |Diff | Sum_Products |Diff|
+------+-------+------------+-----|------------+----|---- |----+--------------|
| 2013 | | | | | | | |
| 2013 | | | | | | | |
| 2013 | | | | | | | |
+------+-------+------------+-----|------------+--- |-----|----+---------| ----
View 3 Replies
View Related
Jan 21, 2015
Got following query:
SELECT
event_data.value('(event/data/value)[4]', 'bigint') AS cpu_time,
--database name
event_data.value('(event/data/value)[5]', 'bigint') AS duration,
--estimated cost
--estimated rows
--nest level
[code]...
Basically, is a simple T-SQL query that reads the local file for my already setup extended event sessions. But I can't find the way to retrieve the following attributes as part as the T-SQL query:
--database name
--estimated cost
--estimated rows
--nest level
--object name
I am trying to find a BOL or some MS link with the full list of possible values for event_data.value but can't find one.
View 2 Replies
View Related
Jun 11, 2015
Script to find the details of creation date and modified date of all files located in a path?
I wanna write few custom messages before I delete some files from a path.
View 9 Replies
View Related
Jun 15, 2005
Is it possible to have an auto increment alphanumeric primary key eg A1, A2, A3
Thanks
Paul.
View 1 Replies
View Related
Apr 9, 2008
I have set a column called "Anumber", I am
using a computed value to get the PK ID # and
a the letter "A" to it.
('A'+CONVERT([varchar](10),[requestid],(0)))
The above works fine unless I copy a record,
The copied record never displays the correct
value like "A55" it keeps "NULL" as it value.
Is their a better way to achieve a result as
described above ?
I am coming from Foxpro to SQL, Any advice would
be great.
Thanks
View 6 Replies
View Related
Apr 10, 2008
Hi experts,
I am executing a script to add a column to few tables and drop existing constraints, create new foreign key constraints, indexes etc. After we test this on our development database we have to deliver the script to the customer.
My concern here is, when I have to drop the constraint with the name, I am not able to do it because its been created with alphanumeric char in the constraint name. So I end up getting the constraint name for each table. And moreover,
I wont be able to deliver the same script to the customer as the alphanumeric in the constraint names on the customer's database will be different.
Any suggestions to overcome these alphanumeric in the constraint names?
Thanks much
View 20 Replies
View Related
Jan 10, 2008
Greetings all,
I have upsized 2 foxpro table to SQL Express.
In table 2 called Orphan I have/had a field that
would autonumber with the letter "U" first.
Example would create a AlphaNumber Like "U00001223"
In Foxpro this is a easy function, But I have been
unable to find a similiar way to do this in SQLEXPRESS.
Foxpro would have you define the field as Unique Number with
template defintion of "U"N8
In SQL I know you can use Numeric on a field for a unique number
but cant find setting to proced number with a "U"
Is this doable is in SQL and does anybody have a
reference point or example they could share ?
Any help would be great and hope my question made sense..
View 8 Replies
View Related
Dec 20, 2007
All,
I'm having trouble with a query where I need to limit a resultset by comparing (using a WHERE clause) a field that is alphanumeric with one that is numeric. I've tried converting, casting, and case statements, but I either get an overflow error or a big slap on the wrist by SQL Server 2005. Does anyone have any good solutions to this? I've been racking my brain for a while now.
Thanks for the help!
-Scott Mescall
View 3 Replies
View Related
Feb 3, 2007
Hello,
I have a SQL database with about 300 company names and corresponding phone numbers. I would like to show a list of linkbuttons titled A-Z and when pressed, rebind the sqldatasource so that my GridView will only show company names that start with that letter.
I know there are some examples on codeproject.com, but they are a bit over my head... besides, I don't mind writing a custom select statement for the OnClick of every linkbutton if that's what I have to do. Problem is I haven't a clue how to write a select statement that will return items who's first letter matches my desired letter?
Any idea?
Thanks,
-Derek
View 3 Replies
View Related
Oct 26, 2004
Hi there,The age old question of creating a unique alphanumeric value automatically like ABC0001, ABC0002 Is it possible to do this automatically? That is, without having to update it which will slow the db down horribly?
View 4 Replies
View Related
Nov 27, 2014
I'm using SQL 2008 with table [AgentDetails] and fields [IDCode],[FirstName],[LastName],..etc. [IDCode] is alphanumeric [AAA001].
Is it possible to increment both alpha & numeric when new record is inserted. e.g.
AAA001,AAA002......AAA999,AAB000,AAB001,...AAB999,AAC000,AAC001...etc. with a user function or some stored procedure.
View 2 Replies
View Related
