Left Most Character Is An Alpha Or Number?

Feb 23, 2006

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

View 2 Replies


ADVERTISEMENT

Get Index Of First Alpha-numeric Character

Sep 29, 2005

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!

View 1 Replies View Related

Return Left-most Character From 8 Character String

Oct 1, 2014

I'd like to return the left-most character from an 8 character string & the third from the left character too.

Like this ABC00123 returns AC

$query = "SELECT LEFT(uninum,3), RIGHT(uninum,5), clmarea, Date FROM tblunimov";
$result = mysql_query($query) or die(mysql_error());

echo "<div class='tblstyle1'>";
echo "<table class='tblstyle1'>";
echo "<tr><th>ini</th><th>item</th><th>area</th><th>date</th></tr>";
while($row = mysql_fetch_array($result)){

[Code] ....

View 5 Replies View Related

Anything Left Of A Character?

Apr 18, 2007

if i had many variables like 12-232
and 232131-232

and wanted only that left of the - symbol from that variable how could i do that?

View 5 Replies View Related

Pattern Matching - Searching For Numeric Or Alpha Or Alpha-Numeric Characters In A String

Aug 18, 2006

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






View 1 Replies View Related

How To Spell-out Number Into Character

Dec 6, 2011

actually 123 will come as one twenty three in words & what functionality using in sqlserver2008

View 4 Replies View Related

To Find The Number Of Occurences Of A Particular Character In A String

Aug 8, 2001

Hi all,

@str varchar(500)

select @str = 'abcd,efgh,i,jklmn,opsqrtuv'

For the above string, which string function can be used to find the number of occurences of a particular character, for example, ','? For this example, the answer should be 4. Is there any built-in function in SQL to do this?

Thanks in advance,
-Praveena

View 1 Replies View Related

How To Calculate The Character Number In The Field Column

Apr 25, 2008



Hello:

I have a field hold varchar. I would like to calculate how many character in the field when user enter the data and save in the database.

Is there any way to do?

Thanks,

Snow

View 3 Replies View Related

Reporting Services :: How To Convert Character Value Into Number Value

Aug 2, 2009

I have a field that contains number but it actually is character. I want to convert it to number so i can put it into query like this:

sum(field) as Totalmyfield.

View 6 Replies View Related

Concat With A Loop - Convert Number Into String Character

Apr 5, 2014

I'm new to SQL and I've been trying this for a while now.

I have let's say a loop of numbers from one to ten. It appears like this:

1
2
3
4
5

What I want to do is to have it appear on one column like this: 12345, the problem is I can't seem to figure out if I need to only have one variable or I'm missing something else, I figured you need to convert them into a string character but I'm still unable to do it.

View 7 Replies View Related

SQL Server 2012 :: Find Number Of Times Character Appears In A String

Apr 11, 2015

In my staging table I am having data like below

ABL¯ABL¯0¯0¯ABL¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ABQ¯480¯825¯DLS¯AMA¯ABQ¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ACD¯808¯1255¯DLS¯ELP¯TCS¯PHX¯ACD¯ ¯ ¯ ¯ ¯
ABL¯ADE¯1256¯471¯DLS¯AMA¯ABQ¯LSV¯ADE¯ ¯ ¯ ¯ ¯
ABL¯AFT¯1140¯1744¯DLS¯LAX¯FON¯AFT¯ ¯ ¯ ¯ ¯ ¯
ABL¯AHM¯1178¯1637¯DLS¯LAX¯AHM¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ALB¯1769¯1825¯DLS¯WIL¯ALB¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ALE¯1041¯1150¯DLS¯ALE¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯

Now I want to find the Number of times a '¯'character appears in a string. I should get output 14

View 2 Replies View Related

Transact SQL :: Select From A Select Using Row Number With Left Join

Aug 20, 2015

The select command below will output one patient’s information in 1 row:

Patient id
Last name
First name
Address 1
OP Coverage Plan 1
OP Policy # 1
OP Coverage Plan 2

[code]...

This works great if there is at least one OP coverage.   There are 3 tables in which to get information which are the patient table, the coverage table, and the coverage history table.   The coverage table links to the patient table via pat_id and it tells me the patient's coverage plan and in which priority to bill.  The coverage history table links to the patient and coverage table via patient id and coverage plan and it gives me the effective date.  

select src.pat_id, lname, fname, addr1,
max(case when rn = 1 then src.coverage_plan_ end) as OP_Coverage1,
max(case when rn = 1 then src.policy_id end) as OP_Policy1,

code]...

View 6 Replies View Related

Transact SQL :: Left Pad With 0 And Extract 2 Characters On The Left?

Oct 28, 2015

I have a table PLACE with a character column CODE

[Code] [nchar](4) NULL

I need to left pad the column with 0 if it is less than 4 characters long and extract the first 2 characters on the left into a new column COUNTY_CODE.

How can I do that in transact SQL?

I tried:
     
SELECT  RIGHT(RTRIM('0000'+ISNULL([Code],'')),4)     
   FROM [Place]
   WHERE [Place Code]='B' and [Code]='627'

And I got 0627. And how do I extract the first 2 characters?

View 10 Replies View Related

User NOT LIKE (alpha)

Jan 19, 2007

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

View 2 Replies View Related

Alpha Prefix - Can It Be Done In SQL?

Jun 14, 2007

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?

View 3 Replies View Related

Remove Space Left To Right And Right To Left

Oct 24, 2013

How to remove space left to right and right to left

If I give limit >60 for first 60 character; limit 60< second 60 character

Result would be check if space at 60 character if yes remove and go the 59 character check then space remove and 58 character check if there is charater then display

As well as after 60 character to till 120 for right space

View 5 Replies View Related

T-SQL (SS2K8) :: Find String Before Character When Character Appears Multiple Times

May 17, 2015

I have a table that contains file paths as

ServernamefolderAfilenameA
ServernameFolderBFilenameB

and I need a query to return

ServernamefolderA
ServernameFolderB

I tried

SELECT DISTINCT left(Source, charindex('', Source)- 0) AS String
FROM Table

But that removes everything after the first and I need it to return all data before the last

View 5 Replies View Related

Alpha Numeric Function

Oct 14, 2004

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

View 2 Replies View Related

T-SQL (SS2K8) :: Replace Multiple Occurrences Of Same Character With Single Character

Aug 6, 2015

I have the following scenario, The contents of main file are like :

ServerCentral|||||forum|||||||||||||||is||||||the||best
so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it

And I need the output in the following form:

ServerCentral=forum=is=the=best
so=be=on=it

The logic being that multiple and consecutive occurrences of the special character, here - pipe , should be replaced by a single special character.

View 5 Replies View Related

Migrate Alpha Sql 6.5 To Intel Platform

Jan 7, 2002

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

View 1 Replies View Related

SQL Transfer From Alpha To Intel Platform

Oct 19, 2000

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

View 1 Replies View Related

Migrating Sqlserver 6.5 From Alpha To Intel

Jun 20, 2000

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

View 2 Replies View Related

Backup From Alpha, Restore On Intel??

Dec 10, 1999

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

View 2 Replies View Related

Can I Install SQL Server 6.5 On Alpha System With Fx!32

Dec 23, 1998

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

View 2 Replies View Related

Trigger To Seperate Alpha From Numeric

Nov 9, 2007

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


View 5 Replies View Related

Select Part Of Character String Based On A Character

Apr 15, 2004

I have data in a column that starts with 1-4 characters followed by a dash then followed by an number of characters (ex: EU-Surgery).

How do I select everything to the right of the dash when the number of characters to the left of the dash varies?

View 3 Replies View Related

Left Join Vs Left Outer Join Syntax Generates Different Execution Plans

Apr 16, 2008



Anyone know why using

SELECT *
FROM a LEFT OUTER JOIN b
ON a.id = b.id
instead of

SELECT *
FROM a LEFT JOIN b
ON a.id = b.id

generates a different execution plan?

My query is more complex, but when I change "LEFT OUTER JOIN" to "LEFT JOIN" I get a different execution plan, which is absolutely baffling me! Especially considering everything I know and was able to research essentially said the "OUTER" is implied in "LEFT JOIN".

Any enlightenment is very appreciated.

Thanks

View 5 Replies View Related

Indexing - What Is Fastest? Numeric Or Alpha Fields?

Jul 27, 2006

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

View 1 Replies View Related

Determine If Values In A Field Are Alpha Or Numeric

Feb 28, 2008

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

View 4 Replies View Related

Removing Non-alpha Characters && Spaces Script...

Dec 6, 2006

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

View 2 Replies View Related

Select Statment On Column That Can Contain Alpha And Numeric Values

Sep 13, 2007

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! 

View 5 Replies View Related

SQL Server 2012 :: Remove Non Alpha Chars From A Column

Mar 1, 2014

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]....

View 9 Replies View Related

Remove Alpha Chars &&amp; All Spaces In Field [UPDATED]

Apr 5, 2007

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?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved