To Find The Special Character Inside A Name

Mar 21, 2006

CHALASANI writes "I have a name in my table which is "ra_ja" I want to find the name with the help of the'_' character.

Can you please help with the querry

Sorry with my english
Thank you"

View 4 Replies


ADVERTISEMENT

Patindex To Find Special Character And Next 6 Digits

Dec 6, 2007

I am creating a view to pull data for a UPS integration I am doing. I currently have this view where I pull my data from. All is well accept for my PATINDEX,

Currently I have this PATINDEX So when a user puts this into delivery instructions "#999999" UPS_FINAL returns 999999 which is good for me to use. But The PATINDEX will also grab all kinds of neat stuff out of the delivery instructions field when I really only want whatever is after the # sign in the field.

I am new to sql and dont quite understand how this search is working but I think I am in need of a better way to search the field



Code:


WHEN SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions, PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) IS NULL

THEN dbo.Address_Table.ups_code

ELSE SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) END AS UPS_FINAL


-------------------------------------------------------------------------------------
What I am looking to accomplish is a SUBSTRING that will search delivery_instructions



Code:


SELECT

TOP (100) PERCENT P21PLAY.dbo.p21_view_oe_pick_ticket.pick_ticket_no,
P21PLAY.dbo.p21_view_oe_hdr.order_no, P21PLAY.dbo.p21_view_oe_hdr.customer_id,
P21PLAY.dbo.p21_view_oe_hdr.ship2_name, P21PLAY.dbo.p21_view_oe_hdr.ship2_add1,
P21PLAY.dbo.p21_view_oe_hdr.ship2_add2,
P21PLAY.dbo.p21_view_oe_hdr.ship2_city,
P21PLAY.dbo.p21_view_oe_hdr.ship2_state,
P21PLAY.dbo.p21_view_oe_hdr.ship2_zip,
P21PLAY.dbo.p21_view_oe_hdr.po_no,
P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id AS Carrier,
P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id AS Supplier,

P21PLAY.dbo.p21_view_oe_hdr.cod_flag,
P21PLAY.dbo.p21_view_oe_hdr.terms,
P21PLAY.dbo.p21_view_oe_hdr.ship2_country,
P21PLAY.dbo.p21_view_oe_hdr.ship_to_phone,
P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
dbo.Address_Table.ups_code,

-----------Looks for special character and returns next 6 spaces as UPS_Shipper----------

SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions, PATINDEX('%[^a-z ]%',

P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6)
AS UPS_Shipper,

------------------Checks view for email address or assigns alternate------------------
(CASE WHEN charindex('@', p21_view_contacts.email_address) > 0 THEN p21_view_contacts.email_address ELSE

'email@domain.com' END) AS alternate_address,

'Y' AS QVN, 'email@domain.com' AS failureaddress,

P21PLAY.dbo.p21_view_contacts.email_address,

------------When carrier_id is not one of these # then Null; else ------------------------------

CASE WHEN P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id NOT IN (105188, 105191, 105194, 105197, 105200,

105203, 105206, 105209, 105212) THEN NULL

----------------- Looks for special Character in delivery_instructions; if NULL then ups_code;
ELSE return value from delivery_instructions as UPS_Final--------------------

WHEN SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) IS NULL
THEN dbo.Address_Table.ups_code
ELSE SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) END AS UPS_FINAL


FROM dbo.Address_Table INNER JOIN
P21PLAY.dbo.p21_view_oe_pick_ticket INNER JOIN
P21PLAY.dbo.p21_view_oe_hdr ON P21PLAY.dbo.p21_view_oe_pick_ticket.order_no =

P21PLAY.dbo.p21_view_oe_hdr.order_no ON
dbo.Address_Table.id = P21PLAY.dbo.p21_view_oe_hdr.customer_id LEFT OUTER JOIN
P21PLAY.dbo.p21_view_contacts ON P21PLAY.dbo.p21_view_oe_hdr.contact_id = P21PLAY.dbo.p21_view_contacts.id

WHERE
(P21PLAY.dbo.p21_view_oe_hdr.completed <> 'Y')
AND (P21PLAY.dbo.p21_view_oe_hdr.delete_flag <> 'Y')
AND (P21PLAY.dbo.p21_view_oe_hdr.will_call <> 'Y')
ORDER BY P21PLAY.dbo.p21_view_oe_pick_ticket.pick_ticket_no



Hope this makes since

View 4 Replies View Related

Find The Occurrence Of A Particular Character Inside A String

Dec 5, 2007

Hi all,

Am searching for a built-in function that retrives me the occurrence (counts) of a particular character inside a string.


For e.g. In the String 'SqlServer'. I want to find the no of the character 'e', which shud retrive the value as 2.

Can this be acheived through any function.

Cheers !
ash

View 6 Replies View Related

Special Character

Dec 8, 2005

Hi,

i have the following problem:

I like to extract all special characters in one table with many fields.
How can I handle this without using 'replace' for each field and many characters ?

Thx. for ur suggestions.

dajm

View 2 Replies View Related

Special Character

Jul 20, 2005

How can I insert by asp a string containing 'That's the stringAsp code:SQL = "insert into tablename (columnA) values ('" & variable & "')"The problem is when variable contains a ' (single quote), it stops thestring definition and get an error.Can I do something?Thanks--Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

View 2 Replies View Related

Special Character;strange

Oct 6, 2004

I had to update pricelist at local-db on the basis of prices in another SQL by matching the description. When i JOIN the tables, no record gets displayed, but the descrptions at both tables were exactly the same i believed. Took a lot of time in identifying the discrepancy; the data was like this:

CREATE TABLE #ABC (price money, DESCRIPTION VARCHAR(200))
GO
INSERT #ABC SELECT 19999,'SQL2000
'
GO
SELECT * FROM #ABC WHERE DESCRIPTION ='SQL2000'
--(0 row(s) affected)
SELECT * FROM #ABC WHERE LTRIM(RTRIM(DESCRIPTION))='SQL2000'
--(0 row(s) affected)
select * from #ABC WHERE REPLACE(DESCRIPTION,'
','')='SQL2000'
--(1 row(s) affected)
GO
DROP TABLE #ABC
:rolleyes: I believe, ENTER has some value within a field.

View 14 Replies View Related

Replace Special Character

Feb 3, 2006

I'm trying to remove the special character ÿ from a varchar

select replace(my_col,'ÿ',' ')

this works, but the problem is that it also seems to replace the 'normal' y

(Database is case sensitive)

Why is that?

View 3 Replies View Related

Special Character Replacement

Mar 3, 2006

shailendra writes "Hi,everbody
lets come to my problem.I have one variable of type "ntext" which contain character "". i want to remove it or replace it with a blank character.

very urgent.....
waiting for the reply
shail"

View 1 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

Special Character '[' In MSSQL 2005

Nov 7, 2007

Hi,

I am using MSSQL 2005 with the compatability mode set to 2000.Now, one of my String columns in a table has data in the following format:

ABC[123][XYZ]

Now issuing the following SELECT query in the table does not work :

Select * from TAB1 where col1 like 'ABC[%'

ie, no row gets selected.However, the following query works:

Select * from TAB1 where col1 like 'ABC%'

Looks like '[' is a special character :( Now, how do i work around this so that i can select strings starting with "ABC[".Please help.

Thanks.

View 5 Replies View Related

Would Special Character Cause Insertion Problem?

Oct 12, 2007

Hi,SQL env: sql server 2000Target column of insertion: varchar(15)Case, a var is made up of a character of the following characters(random selection):A,B,C,D,E,$,!,%,^,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U, V,W,X,Y,Z,&,*,(,)and a few numbers (random selection), and thenvar = ran1&ran2I'm experiencing intermit sql errorIs it because I did not include the string with quotes likevar = "ran1 & ran2"or the first random seletion may include special character and thatmay cause insertion error randomly?Probably the former is more likely. And I've added quotes for thevar (programming language shouldn't really matter, int type usuallywithout quotes while strings need quotes).What's your thought?Thanks.

View 7 Replies View Related

Special Character In Dynamic TSQL

Oct 8, 2007

Hello all



I am trying to update a colunm with the value (Dynamically).



'UPDATE ' + @TABLE + ' SET '+ @FIELD + ' = '''+ @VALUE +'''

well It works fine until @Value contains quotation for instance @value = O'hare Airport
results in termination of the statement because of single quote after O in O'Hare. Is there any way I can see it works


Also suppose if its updating a field which can be say 10 charcters long and when @value has say 15 characters, it terminates. Is there anyway i can avoid this.




View 4 Replies View Related

Transact SQL :: How To Get All Characters Before Special Character

Jul 21, 2015

I would like to know how can i only get the characters before the special character?

For example if the mail id is The.Champ123@gmail.com i need to extract The.Champ123 and if the mail id is TheChamp@gmail.com I need to get TheChamp.So basically i would like to get the characters from the string before '@' character.

View 14 Replies View Related

T-SQL (SS2K8) :: Insert Value Generate Special Character

Mar 12, 2014

I have a web page where the user can select the language (FR, EN, BG, ...) in a drop down list.

Next to the drop down list there is a text box where user can type the some text (translation).

User can add several description

On my web page, i have a button this button collect all information create an xml file and save all in database (sql server 2008)

that's work fine for some language => FR, EN and so on

But for bulgarian (bulgare) and greece there are some problem...

Some characters when i display it in sql look like => ???s??. ? d??ta?? a?t?

The value encoded by the user is => Ένωσης. Η διάταξη αυτή

but the result after t sql xpath is => ???s??. ? d??ta?? a?t?

Here is it my sql code where you can find my temporary table and my xml file and my xpath query

declare @tblTranslation table (idDocID int, languageID varchar(10), value varchar(500))
declare @Translations XML

set @Translations = '<?xml version="1.0" ?><Items><Item><eleKey>EN</eleKey><eleValue>This is a test</eleValue></Item><Item><eleKey>FR</eleKey><eleValue>test</eleValue></Item><Item><eleKey>BG</eleKey><eleValue>Ένωσης. Η διάταξη αυτή</eleValue></Item><Item><eleKey>HR</eleKey><eleValue></eleValue></Item><Item><eleKey>RO</eleKey><eleValue></eleValue></Item></Items>'

-- 2) fill the temporary table with information from the xml file

INSERT INTO @tblTranslation(idDocID, languageID, value)
SELECT
1
, Convert(nvarchar(max), i.query('eleKey/text()')) as colKey
, Convert(nvarchar(max), i.query('eleValue/text()')) as colValue
--
FROM @translations.nodes('/Items/Item') as x(i)
SELECT * FROM @tblTranslation

View 3 Replies View Related

T-SQL (SS2K8) :: Replace A Special Character In Results?

Jun 11, 2014

I have an database that is housing a path used to locate an external file. This application was written many years ago and I am now trying to bring the files into the database as a VARBINARY.

The table is holding the path like this "/folder/folder/file"

I am trying to convert that path to "folderfolderfile"

In my Select statement I have

SELECT ProdID, REPLACE (PATH, /, ) FROM dbo.blahblah

The problem is that I can't figure out to make SQL understand that "/" is the character I want to replace.

View 2 Replies View Related

Transact SQL :: Extracting Certain Text After Special Character

May 18, 2015

I would like to write a SQL to extract a text after certain _ special character.Example : Relationship_Person_Type_Aunt

View 9 Replies View Related

Data Access :: Special Character In Password

Jul 9, 2015

There is a ">" character (right-angle bracket) inside my SQL Server password. When I supplied this password to the bcp utility, the ">" character was treated as an output redirection symbol. So the password was truncated at ">" and the bcp output got sent to a file with a name consisting of the rest of the password after ">". I'm using SQL Server 2012. I cannot use Windows authentication due to company policy. Is there a way to resolve this without changing the password?

View 7 Replies View Related

Saving Text With Special Character Into SQL Express Table

Jun 22, 2007

Hi, fellows!



When I need to save a text into a SQL Express table I use the character ' to encote the text. (ex: 'myText' )



How to procedure when the text has already the character ' ? (ex: how to enconte the text Color's car ) ?



Many thanks to any kind of help!

View 3 Replies View Related

NTEXT Special Character SQUARE Appearing Where A Return Should Be. How Do I Fix This?

Sep 17, 2006

I had to import some FileMaker Data into SQL Server 2005 and in the resume field a "SQUARE" special character is appearing everyplace that a return should be.

It has really messed up the formatting. I know how to use the updatetext in a cursor to replace the special character but I don't know what to replace the "SQUARE" special character with so the text is displaying a new line rather then this "SQUARE" character.



Any help would be appreicated.

View 6 Replies View Related

Problems Character Sets / Special Characters Dot.Net &<-&> Mssql-server

Feb 11, 2008

Hi,I come from the "dark side" php/mysql and there often problems withcharacter sets (utf-8, latin...) and storing data in datebase.Exists in the world of dot.net and ms-sql-server similiar problems?To precise: I have to store xml-data in database. Maybe its better toencode (like base64) the strings?Perhaps there are some links to read?Thanks.klaus.

View 3 Replies View Related

How To Specify A Special Character As A Row Delimiter In The Flat File Connection Manager?

Jan 16, 2007

I have a gazillion text files, each with dozens of records. The fields are pipe-delimited, the end of each record is marked by ASCII char code 28.

example:

05|11900307|1|CO|Gervais|Neil|NG8880|F|540|0|0|0|T|0|||F|||F 05|11900307|2|AO|MARSHALL|BRAD|BM7843|F|510|60|0|0|T|0|||F|||F

The " " is the Chr(28). The line above should parse into 2 rows...

**** time passes ****

An interesting discovery - if I paste that " " directly into the row delimiter box, without quotes or brackets, I get two records!

So I found at least one answer.

Is there another?

TIA

View 1 Replies View Related

How To Import In Special Character Delimited Text File By Using SSIS ?

Sep 26, 2007

Hi,

I would like to know how to import in the custom delimited text file by using SSIS.
For example, instead by using tab or comma delimited, I use this character : '¶'
The reason is the delimited format that SSIS provided is too common such as colon, semi colon, tab, comma and pipeline.
I have the data that the user also key in the pipeline there. So I am thinking to separate the field by using this special character, but cannot see if there is anyway to import in by using SSIS.

Please help to share the solution on this :

A¶B¶C
1¶2¶3

thanks
best regards,
Tanipar

View 8 Replies View Related

SQL Server 2012 :: Split String Into Columns Based On Special Character

Dec 4, 2013

How to get the required result in SQL 2012

Create table DBInfo (Path varchar (500))
Insert into DBInfo values('/Data Sources')
Insert into DBInfo values('/Data Sources/SALES')
Insert into DBInfo values('/PRODUCTION')
Insert into DBInfo values('/PRODUCTION/SERVICE')
Insert into DBInfo values('/PRODUCTION/SERVICE/MAINTENANCE')
Insert into DBInfo values('/PRODUCTION/SERVICE/LOGISTICS')

My Expected Output

Column1,Column2,Column3
Data SourcesNullNull
Data SourcesSalesNull
PRODUCTIONNullNull
PRODUCTIONSERVICENull
PRODUCTIONSERVICEMAINTENANCE
PRODUCTIONSERVICELOGISTICS

View 1 Replies View Related

Multiple Rows Combined Into Onerow And Onecolumn Separated By A Special Character

Oct 24, 2007

Hi All :CREATE TABLE TABLEA(Person Varchar(20), Country Varchar(20), SubjectVarchar(20), Type Char(1))INSERT INTO TABLEA VALUES ('Einstein', 'Germany', 'Physics', 'P')INSERT INTO TABLEA VALUES ('Kant', 'Germany', 'Philosophy', 'Q')INSERT INTO TABLEA VALUES ('Kafka', 'Germany', 'Writer' , 'W')INSERT INTO TABLEA VALUES ('Aristotle', 'Greece', 'Philosophy', 'Q')INSERT INTO TABLEA VALUES ('Archimedes', 'Greece', 'Physics', 'P')INSERT INTO TABLEA VALUES ('Homer', 'Greece', 'Writer' , 'W')SELECT * FROM TABLEAI am on SQL 2000.I need an output where i have to have a resultset grouped on Type, butthe results in one row.In the resultset I needTypeP PersonType P Country, Type Q Person, Type Q Country, TypeW Person Type W Country---------------------------------------------------------------------------------------------------------------------Einstein:ArchimedesGermany:GreeceKant:Aristotle Germany:GreeceKafka:HomerGermany:Greece************************************************** *************I have written a puesdo-cursor code to do the same, but if there is away to do as a set operation, that would be greatPlease select as a whole and past in query analyser as the resultsetis all overlaid when i paste in this box.Thank youRS

View 2 Replies View Related

Special Character Problems With SSIS Data Reader Using ODBC Driver For DB2

Sep 5, 2007



Hi,
in SSIS I read data from a DB2 database on AS400 using the Client Access ODBC Driver for DB2 from IBM and write it to a SQL Server database. Since it does not work using the odbc driver as data source directly, I use a data reader component with .net providersodbc.
Some special characters were not translated correctly when read from DB2. They show up as ? in the SQL Server target table.


I tried to change the client locale in the ODBC connection properties but it did not help me. I tried changing all other settings in odbc but it still does not work.


In dts I could source all the data without this problems and good speed using the same nodbc driver.


The OLEDB Providers delivered with SSIS do not work in SSIS or I am too stupid to configure them correctly. They are even too slow as I explained above.


I cannot use the MS OLEDB Provider for DB2, because it works only in Enterprize Edition and we only have the Standard Edition.


Thus, only using Client Access ODBC Driver for DB2 with net providersodbc (as bridge) is performant enough and works on Itanium. But how to work around the problem with the special characters?


Best regards,
Stefoon

View 3 Replies View Related

Integration Services :: SSIS - Extracting From Excel Begin At A Row Include A Special Character?

Sep 18, 2015

I want to import date from excel file  from the row if this row has a character like 'TableName', and I don't know the rownumber and end column number ,so I can't use openrowset 'select * from Sheet$ A11:N'.   And I am thing if I can get the rownumber of the row contain  string 'TableName' and the max column ,that May be easy to extract data.

View 2 Replies View Related

Replace A Character Inside A Field

Mar 24, 2008

Hi, I need to replace a character from a column of a txt file. I have defined the column and the values (datetime type for sql server) that I receive are like this: 2008-02-15-20.07.19
So, I need to replace the "." with a ":" beacuse those are minutes and sql server uses ":" for minutes
How can I do this? Any help?
Thanks

View 9 Replies View Related

How To Count # Of Occurrences Of A Character Inside A String?

Jan 24, 2008

I have a string of characters in my data flow and I need to add a derived column showing the # of times a certain character appears within that string. For example, my string in the data flow is:

NNNNNRJKSURNNNEJNNNN

Now I need to count the number of "N"s in that column. From the example above, I should get the integer 12, and that would be the value of my derived column. Any ideas?

View 7 Replies View Related

Find All Special Characters In Table

Jan 23, 2015

I would like to find all Special Characters in a table. I am currently using the code below. Not sure if this gets all of them.

select top 50 *
from [dbo].[SV20150122]
where street LIKE '%[^0-9a-zA-Z @.-]%'

View 1 Replies View Related

Character Limit For Variables Inside A Stocked Procedure

Feb 23, 2007

I am currently having a problem where my SQL server seems to lock any variables to 1000 characters (ie. varchar(8000) can only hold 1000)I have read in numerous sources it was possible to change that limit so the varchar can truly hold the 8000 characters and not stop at 1000, but there was no info on how to do this.I am looking for a "How to" to put this limit to 8000.Thank you! 

View 10 Replies View Related

Find All Special Characters And Replace With Space?

Feb 7, 2014

i need to find and all special characters and replace with space.

Following query works well for me to finding all rows having special char.

SELECT DESC FROM TBL_DESC
WHERE DESC LIKE '%[^A-Z0-9 ]%'

replacing special char with space . i need to remove special chars only.

View 3 Replies View Related

SQL Server 2008 :: Use Cursor To Run Dynamic To Find Special Characters Used In Database

Apr 16, 2015

I need to find all uses of special characters in a database. I used the following code to do this:

USE dbName
GO
IF OBJECT_ID('tempdb.dbo.#Results') IS NOT NULL DROP TABLE #Results
GO

[code]...

This will check all tables in the database, but if you want to check specific tables you can uncomment the line in the where clause and specify tables to be checked. The query will return any text fields that have any characters other than letters, numbers or spaces.

This code works fine for me because all the tables in my database have single column primary keys. However I know how much Jeff Moden hates cursors or RBAR queries, so my question is could this have been done by any method other than using a cursor?

View 9 Replies View Related

Find Character

Apr 12, 2008

Hi all,

I have a string like '10010000001000'.
Here I need to find the 4th character of this string (in this case it should be '1') by using sql query. How to do this one?


regards
Shaji

View 4 Replies View Related







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