Select All Non-alphabetic Characters?

Apr 26, 2006

I am trying to write a stored procedure that will return all records that don't start with an alphabetic character.

This is what I have so far but I can't figure out how to make it so
that it will select if the first letter in a column is not a letter.

SELECT strArticleID, strTitle

FROM dbo.tblArticles

WHERE LEFT(strTitle,1) LIKE ? AND bitState = 1

ORDER BY strTitle

I thought about making a temp table and putting all the records in it
if the starting character is not a letter by using a bunch of if
statements but I am hoping there is a cleaner way.

I was hoping there would be an expression or something that could be written in place of the "?" similar to .net expressions.

Can anyone offer help on this?

thanks

View 1 Replies

Searching With Non-alphabetic Characters

Jan 2, 2004

I need to create a Stored Procedure that will search for usernames in my database. I need it to return usernames where the first characters is ANYTHING BUT alphabetic characters (example: numerics, #, !, & etc.).

How can I do this in T-SQL?

View 2 Replies View Related

Can't Contain Non-alphabetic Chars

Jun 11, 2008

With this procedure,can't show if GiftTitle contains space ,nonalphabetic chars(&,-,..) and if start with numnber.
How can i modify ??
Plz help me.
Thanks.

CREATE PROCEDURE dbo.trymatrix AS
DECLARE @SQLCommand varchar(4096), @column sysname

SET @SQLCommand = 'select c.CustomerID as Cust_ID,Name as Co_Name, ' + char(13)

DECLARE curGift CURSOR STATIC LOCAL FOR

select distinct GiftTitle from Redemption1

OPEN curGift

WHILE 1 = 1

BEGIN

FETCH curGift INTO @column

IF @@FETCH_STATUS <> 0

BREAK

SET @SQLCommand = @SQLCommand +

'sum(case GiftTitle when ''' +@column +''' then 1 else null end) as '+ @column + ','+char(13)

END

DEALLOCATE curGift

SET @SQLCommand = left (@SQLcommand, len(@SQLcommand)-2) + char(13)

SET @SQLCommand = @SQLCommand +

'from Customer c

inner join Redemption1 d

on c.CustomerID=d.CustomerID

group by Name,c.CustomerID

order by c.CustomerID'

PRINT @SQLCommand

EXEC (@SQLCommand)

View 4 Replies View Related

Query - Find A Alphabetic Charther

Jul 19, 2007

Morning/Afternoon

Hope you can help me, am current working on ISIN numbers it doesn’t matter if you don't know what they are.

Now I have a table that is like this

table name = tDocISIN
col1 = LID(int)
col2 = sISIN(varchar(50))
col3 = lDocumentId(int)

Which has sample data like this

84237USX7318VAA4543997395
92579USO45167AR4544021575
84244USY63651114644025820
84243USY63651AA4544025820
113633USP51225AA1144028865
92846USG36912AG1344029240
92847US359860AG8444029240

Now I want to run a select query that will only bring the ones that have ‘CA’ or ‘US’ as the first two characters.

I can do that by running this:

select * from tDocISIN
where sISIN like 'CA%'or sISIN like 'US%'

But my other where clause I want to add is only bring back the ones that have CA or US as the first two characters and any other character after the first two or in the rest on the string.

So US7636516745 is an ISIN I don’t want to bring back and US763TY16745 is an ISIN I do want to bring back.

The character that does exist after the first two characters will be anywhere within the rest of the string.

Hope that is clearly explain any help would be great.

Am a total beginner. Thanks in advance.

Lee

View 6 Replies View Related

Select Characters Between...

May 19, 2005

Ok.. I inhertited a program that has columns called cpt_codes, cpt_to, and cpt_from all of them are varchar fields. The user can input a cpt code and the program checks to see if it fall between cpt_to and cpt_from so00101 would fall betwen 00100 and 00110. The problem these are varchars so the user has a code 0024t and is pulling up between 00100 and 01999 which is techinically correct. However thats not where it's supposed to be. Is there a way to get around this? the sql statement currently in place is:SELECT * fROM CPT_TOS WHERE CPT_FROM <= '0024T' AND CPT_TO >= '0024T'*Not my sql statement or design!!" ThanksRobert.

View 3 Replies View Related

How Do You Select Just The First 50 Characters Of A Column

May 14, 2008

I've looked on line, but can't figure out how to select the first fifty characters of a varchar field in sql. (this seems so easy).. argh..

View 1 Replies View Related

Truncation On Select Statements At 255 Characters

May 28, 1999

I beleive we need a new patch with our SQL 7 but I'm not sure. My ODBC INSERT works fine because the Db has the entire text entry. When I try the select it returns only 255 characters.

Any insights?
Thanks.

Mark Mc

View 1 Replies View Related

Query/select The First 10 Characters In A Field

Jul 25, 2007

I'm comparing two tables and need to compare the first 8 characters of one field in table A to the first 8 characters in another field in table B.

So instead of where 'John Smith' = 'John Smith' it would compare where 'John Smi' = 'John Smi'

I know I've done this before but can't find a good reference.

It's something like this when calling a query within a WHILE loop:

SELECT * FROM blah WHERE FID = '".$row['FID']."' AND LEFT('TRACK', 8) = LEFT('".$row['TRACK']."',8)

Am I close? Any help would be appreciated.

View 3 Replies View Related

SQL 2012 :: How To Select Last Characters From A String

Jun 19, 2014

I have the following string and am trying to select only the characters before the last "</>". How can I do this?

declare @String varchar(500)
set @String = 'Assessed By: Michael Jordan Yagnesh
Reviewed By: Fred Smith
Home Address'

select REVERSE(substring(REVERSE(@String),5,charindex(':',REVERSE(@String))-5))Here is what I tried so far:

[Code] ...

View 4 Replies View Related

SQL Select Command.. Allow X Amount Of Characters From A String

Jan 17, 2008

Ok so we gotSELECT this, that, others FROM some.database WHERE this=@this So in the database the others field is a string that can have up to 200 characters, but on this particular data pull I only want to pull the first 50 characters of the others field. How can I do that? Thanks.

View 4 Replies View Related

Finding Special Characters With A SELECT Statement

Mar 12, 1999

Hey Swynkers,

I've got a tricky one. I'm trying to do a SELECT statement to return all of our email fields that include the Newline character, or Char(13). I've tried all of the following with no result:

WHERE Email like "%Char(13)%"
WHERE charindex(Char(13), @Email) > 0
WHERE patindex(@NewLine, @Email) > 0

Any suggestions?

Thanks,

Fenderson

View 3 Replies View Related

SELECT Query Involving Non-English Characters

Apr 8, 2008

Hello friends,

I am inserting non-english strings into my database table from my java program.

Code Snippet
sql = "insert into static_string1 values (?)";
PreparedStatement statement=connection.prepareStatement(sql);
statement.setString(1,statString);

where, statString is a string variable containing Hebrew characters.

Till here, my code works fine. i.e, Hebrew characters are properly inserted to the database.
The problem is when I try to retrieve the String_Id based upon the statString I inserted to the table static_string1.

Code Snippet
String sql = "select String_Id from Static_String1 where String like ('" + statString +"')";
Statement statement=connection.createStatement();
ResultSet rs=statement.executeQuery(sql);
rs.next();
int stringId=rs.getInt("String_Id");

I tried hardcoding the string in the query and to execute it from the SQL Server Management Studio as below

Code Snippetselect String_Id from Static_String1 where String like( ' הזח' );

But even this is returning null rows, even though the entry is present in the table Please help me out asap.

Please pardon me if this is not the right section to post my doubt. I didnt find any other relevant section here.

View 14 Replies View Related

Select Statement - Column To Display Full Name Is 99 Characters

Apr 14, 2015

Select statement. In my database i am using the employee table. I need my first column to display your full name is 99 characters. so like if the employee is john smith it would display Your Full Name Is 9 characters (including the space).

View 2 Replies View Related

SQL Server 2012 :: Select Case Statement To Remove Part Of String After One Or Two Specific Characters

Jun 3, 2015

I have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'

Example Record 1. Elland **REQUIRES BOOKING IN***
Example Record 2. Theale, Nr Reading, Berkshire
Example Record 3. Stockport

How do I achieve this in a CASE Statement?

The following two case statements return the correct results, but I some how need to combine them into a single Statement?

,LEFT(Address ,CASE WHEN CHARINDEX(',',Address) =0
THEN LEN(Address )
ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'

,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0
THEN LEN(Address)
ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'

View 8 Replies View Related

How Can I Store Over 16000 Characters To Sql Table Field With Language Specific Characters?

Feb 19, 2008

In my application I must store over 16000 character in a sql table field . When I split into more than 1 field it gives "unclosed quotation mark" message.
How can I store over 16000 characters to sql table field (only one field) with language specific characters?

Thanks

View 3 Replies View Related

Separate Lowercase Characters From Uppercase Characters

Mar 5, 2008

Hi everybody,
I would like to know if there is any property in sql2000 database to separate lowercase characters from uppercase characters. I mean not to take the values ��child�� and ��Child�� as to be the same. We are transferring our ingres database into sqlserver. In ingres we have these values but we consider them as different values. Can we have it in sqlserver too?

Hellen

View 1 Replies View Related

Iliminating Characters From Set Of Integers And Characters

Jul 19, 2006

Good day experts,

I wonder if i got an answer for this.
How can i iliminate a letters from a set of integers and characters using a SQL Statement

for ex:

ABC9800468F

is that possible?
is there a function that i can use to iliminate them?

View 3 Replies View Related

Transact SQL :: Replace Column Value From ASCII Characters To Non ASCII Characters In Table?

Oct 22, 2015

I’m getting ASCII characters in one column of my table. So I want to replace same column value in NON ASCII characters.

Note – values in column must be same

View 10 Replies View Related

More Than 255 Characters??

Apr 23, 1999

Hello!

We are trying to build a FAQ in a SQL database that will be updateble trough the web.

Now to the questione:
We can't use more than 255 characters, in Books Online it says that char and varchar supports 1 to 8000 characters but it doesn't work for us.

Anyone knows why?

View 4 Replies View Related

If 5 Characters, Add '0' To The End

Aug 26, 2003

I have a column with data type of text.

Some values are 5 digits long, and other characters are 6 digits long.

I want to write a query that adds a '0' to the end of the values that only have 5 digits...

How would my syntax look like ?

thank you

View 6 Replies View Related

Add Characters

Aug 11, 2005

I have a field oh phone numbers that had no specific format. I have removed all of the previous formatting so know there are just a string of 10 numbers and I want them all to change to "(###) ###-####". Is there an SQL statement that I can use to update the field in a batch process. Any help would be great. I also have linked the tables to an access db, if that makes it easier. Any help is much appreciated.

Thanks

View 2 Replies View Related

Junk Characters

Sep 26, 2007

Hi,
I am Suhasini. While saving data from front end(Asp.net) to back end(Sql server 2005 express edition) i am getting junk characters also added to the database. This character just look like a checkbox. Basically i am adding options using a multiline text box, is there any thing wrong with that. options are saved in the database as junk character followed by option1...... etc. Kindly suggest me on this.

View 3 Replies View Related

How To Get Only The Characters From The Particular Varchar

Mar 13, 2008

Hi,

I have a varchar(10) field in one of the sql2005 table. most of the data will be in the format of

xxxxx{yyyyy}
zzzz{eeeeee}
like above values i am storing into the column. Now i want to use only the value which is inside the brackets { }. Values inside the brackets are not fixed length but allways we use the brackets.

Please let me know if you have any idea.

I tried using the right(value,4).,.. but this is only for the fixed size. but like i said my situation is different length.please let me know if you have any idea.

Thank
-Dil

View 2 Replies View Related

Accentuated Characters SQL

Dec 5, 2003

I use Webmatrix together with Frontpage 2002.
In one of my Webmatrix pages I refer to a field "Prénom" when doing queries.

Sometimes after publishing these pages from my local to my hosted server the é disappears in the source code and SQL server does not find the column.

I have the feeling it has to do with codepage and/or Content.

Has anyone a precise idea.

View 3 Replies View Related

Can't Insert Enough Characters...

Sep 30, 2005

I must be doing something wrong, but I cannot imagine what it could be. I made a brand new table and started to enter some data. I am putting the initail data in using Enterprise Manager. This is essentially test data for development. I have several varchar columns set to 8000 for their size. When I try to enter text into them it is getting truncated at 999 characters. I thought that maybe I misunderstood the varchar type and that 1000 characters is 8000 bits and that is what the 8000 means. So, I redid the table with the datatype "text" for my long columns. Same thing. It is being truncated at 999. Is there something I am missing here? I am not trying to do anything tricky or confusing, I am simply trying to put more than 1000 characters into a table column.I should mention that I have been pasting the text that is copied from Notepad (to remove any extranous formatting). The text cuts cut off and I cannot even type in the column after that.Any clues would really be appreciated.

View 2 Replies View Related

Extraneous Characters

Jun 7, 2001

problem:

Previously I had a problem inserting symbols like an umlaut into a SQL7 DB. The umlaut symbol like in the name [Bj�rk] was translated into [Bj�rk] when the data was bcp'd in. I was able to solve this problem by changing the registry codepage setting for the "OEMCP" from '437' to '1252'.

Fine...but now i have problems with symbols like [�] in the name [Mel Torm�]. The [�] on [Torm�] is changing to [T].....[Mel TormT].

Is there any way of accomidating SQL7 to allow both types of symbols?

thanks for the help...

View 1 Replies View Related

Returning > 255 Characters

Nov 15, 2000

We just upgraded to SQL 7.0 SP2. We enlarged one of our fields from varchar(255) to varchar(500), but when I do a SELECT on the field it only brings back 255. I know this was a limitation in ISQL in 6.5. I tried it in query analyzer and also via a command line. Any ideas how to see all the data?

Thanks

View 2 Replies View Related

Convert Characters

Jul 9, 2001

I've created a procedure that converts chracters that I don't want in a certain field in my db.
The problems is: it also converts characters that I haven't specified
For example: the letter y is converted to u (probably because the letter � (german y) is to be converted to u (� is pronounced as y)).
Does anyone have a solution for this ? Has soundex something to do with this ?

Regards
Carl Nilsson

Here's a sample of the script:

--script begin

CREATE proc bds_convert_names @pass_name varchar(100), @NameStr varchar(100) OUTPUT
as

DECLARE @NotAllowed varchar(100)
DECLARE @IsAllowed varchar(100)
DECLARE @OldChar char(1)
DECLARE @NewChar char(1)
DECLARE @Loop int

-- NAMESTR = Contains the string that should be translated.
SELECT @NameStr = @pass_name
-- Set the name to Lower Cases
SELECT @NameStr = LOWER(@NameStr)

-- Set the characters not allowed
SELECT @NotAllowed = '/:*?"<>|,��'
-- Set which characters that should replace them
SELECT @IsAllowed = '**********aaoeeaauuoi'
-- Set Loop start to zero
SELECT @Loop = 0

-- Start looping, char by char, replacing direct into NAMESTR
WHILE @Loop < LEN(@NotAllowed)
BEGIN
SELECT @Loop = @Loop + 1
SELECT @OldChar = SUBSTRING(@NotAllowed, @Loop, 1)
SELECT @NewChar = SUBSTRING(@IsAllowed, @Loop, 1)
SELECT @NameStr = REPLACE(@NameStr, @OldChar, @NewChar)
END

-- Remove all stars
SELECT @NameStr = REPLACE(@NameStr, '*', '')
GO

--script end

View 1 Replies View Related

SQL Script For # Of Characters

Aug 21, 2001

Hi all,
this might be a very simple query for most of you guys.
What I need help in is that I need to find out records that have a specific number of character within it. For example if a field is 8 char long but there are records that are 3 char long or 4 char long, I need to find those records.

Any help is appreciated
Thanks
Gohar

View 1 Replies View Related

Removing Certain Characters

Aug 18, 2006

Hello all,

I have a table named users. It consists of user names, user ids, etc... The problem is that whoever designed the ASP code before me allowed people to enter info in any format they want. This poses a problem because now the name can have 1, 2, or sometimes 3 spaces in between the last and first names. And sometimes the middle initial is used with a period following it. This creates problems when I am trying to execute a Select statement since it won't match if there are an unknown number of spaces in the string and throws an error when a period is used.

Is there a SQL query I can execute to change all the user_names into a format such as the following:
LastName, FirstName MiddleInitial

Like I said, it is already almost the same, just has too many spaces and some have periods after the middle initial

View 5 Replies View Related

Subtract Last Three Characters!

Jun 24, 2004

Hello everyone. I have a query where I pull all item_codes that start with a 7. I need all the item_codes that start with 7, but I need to subtract the last 3 characters from only the item_codes that are not 71860kit or 71851nggun. How would I be able to accomplish this. What I have below subtracts the last 3 characters from all item_codes including 71860kit and 71851nggun. I want these two to stay in tact when I select them in the query.

This is the Query that I have now!

select datepart(year, date_cust_invoice)as INV_YEAR,
datepart(month, date_cust_invoice)as INV_MONTH,
item_code=left(item_code, len(item_code)-3)
, sum(QTY_SALES)as QTY_SALES_TOTALS, sum(SALES_VAL)as SALES_VAL_TOTALS
from opcsahf
where datepart(year, date_cust_invoice) >= '2003'
and substring(item_code, 1, 1) = '7'
group by datepart(year, date_cust_invoice), datepart(month, date_cust_invoice),
item_code
order by inv_year, inv_month, item_code

All help is appreciated!

View 1 Replies View Related

Strip Certain Characters

Jul 13, 2004

Can anyone tell me how I can strip certain chahrcters from a string

I know I can use replace, but i don't think this is appropriate for what I want to do

For example I have the string

declare @text varchar (100)
select @text = 'word1, word2 & word3'

if i do a replace on the string like this
select @text = 'replace(@text, ',', '')
select @text = 'replace(@text, '&', '')

I end up with the string
select @text = 'word1 word2 word3'

i.e. 2 spaces between word2 and word3

What i want the string to look like is :
select @text = 'word1 word2 word3'

Is there a way i can check for more than one space + characters ( / , &) in one go

many thanks

View 4 Replies View Related

Replacing Characters In SQL

Aug 13, 2004

I am incorporating a perl script loading data into my SQL Server. If I receive a message with a single backslash I know to replace it with a double backslash \. But what if it is a " double quote what do I need to do to get it to appear as is?

Thanks

View 2 Replies View Related