Transact SQL :: Extracting Certain Text After Special Character
May 18, 2015I would like to write a SQL to extract a text after certain _ special character.Example :Â Relationship_Person_Type_Aunt
View 9 Replies
ADVERTISEMENT
Integration Services :: SSIS - Extracting From Excel Begin At A Row Include A Special Character?
Sep 18, 2015I 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 RelatedTransact SQL :: How To Get All Characters Before Special Character
Jul 21, 2015I 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.
Saving Text With Special Character Into SQL Express Table
Jun 22, 2007Hi, 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!
How To Import In Special Character Delimited Text File By Using SSIS ?
Sep 26, 2007Hi,
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
Special Character
Dec 8, 2005Hi,
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
Special Character
Jul 20, 2005How 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 RelatedSpecial Character;strange
Oct 6, 2004I 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.
Replace Special Character
Feb 3, 2006I'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?
Special Character Replacement
Mar 3, 2006shailendra 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"
Special Character '[' In MSSQL 2005
Nov 7, 2007Hi,
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.
To Find The Special Character Inside A Name
Mar 21, 2006CHALASANI 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"
Would Special Character Cause Insertion Problem?
Oct 12, 2007Hi,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 RelatedSpecial Character In Dynamic TSQL
Oct 8, 2007Hello 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.
Patindex To Find Special Character And Next 6 Digits
Dec 6, 2007I 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
T-SQL (SS2K8) :: Insert Value Generate Special Character
Mar 12, 2014I 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
T-SQL (SS2K8) :: Replace A Special Character In Results?
Jun 11, 2014I 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.
Data Access :: Special Character In Password
Jul 9, 2015There 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 RelatedNTEXT Special Character SQUARE Appearing Where A Return Should Be. How Do I Fix This?
Sep 17, 2006I 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.
Problems Character Sets / Special Characters Dot.Net &<-&> Mssql-server
Feb 11, 2008Hi,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 RelatedHow To Specify A Special Character As A Row Delimiter In The Flat File Connection Manager?
Jan 16, 2007I 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
SQL Server 2012 :: Split String Into Columns Based On Special Character
Dec 4, 2013How 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
Multiple Rows Combined Into Onerow And Onecolumn Separated By A Special Character
Oct 24, 2007Hi 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 RelatedSpecial 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
Transact SQL :: Extracting A Word From String?
Jun 30, 2015Is there a function / way to extract a word from a particular position in the String.
I want to extract a word which is in the 16th position. All words are separated by spaces.
Transact SQL :: Customized Search With Special Characters?
Jun 20, 2015I am creating a key-wording module where I want to search data using the comma separated words.And the search is categorized into comma ',' and minus '-'. Take a look on the example what I exactly want to do is
I have a main table name `tbl_main` in SQL
AS_ID KWD
1 Man,Businessman,Business,Office,confidence,arms crossed
2 Man,Businessman,Business,Office,laptop,corridor,waiting
3 man,business,mobile phone,mobile,phone
4 Welcome,Greeting,beautiful,bride,celebration,wedding,woman,happiness
5 beautiful,bride,wedding,woman,happiness,mobile phone,talking
6 woman,girl,Digital Tablet,working,sitting,online
7 woman,girl,Digital Tablet,working,smiling,happiness,hand on chin
If serch text is = Man,Businessman then result AS_ID is =1,2
If serch text is = Man,-Businessman then result AS_ID is =3
If serch text is = woman,girl,-Working then result AS_ID is =4,5
What is the best why to do this?
Transact SQL :: Query - How To Check For Special Characters
Aug 21, 2015query that checks to see if a variable contains special characters; except for hyphens, periods, and accents?
View 3 Replies View RelatedTransact SQL :: Extracting From 3 Related Tables And Including MAX Function
Oct 5, 2015I have these 3 tables in a database for printed chamber music works:
Table INSTRUMENTS: List of musical instruments
  Column ID      -- int, primary key
  Column sInstrument -- string
Table PTRS_INSTRUMENTATION:Â
  Column ID      -- int, primary key
  Column lInstrument -- int, points to a row in table INSTRUMENTS via ID
  Column lMainItem  -- int, points to a row in table MAINITEMS via ID
  Column lInstrSeqNum -- int, sequential number of like instruments within one Main Item
  Â
Table MAINITEMS: List of chamber music works
  Column ID      -- int, primary key
  Column sItem    -- string
  Â
Each chamber music work (Main Item) requires an ensemble of musicians playing several instruments. Some of them may be the same, such as 2 violins. A classical quartet, for instance, consist of 2 violins, 1 viola and 1 cello. I am concerned here only with one of the instruments (although it may occur several times).
I want to extract a list of Main Items where the instrument is given (e.g., 'violin'),
And the maximum of PTRS_INSTRUMENTATION.lInstrSeqNum is a given number (e.g., 2).
Transact SQL :: Extracting Specific Data From One Field Using Charindex
Jul 20, 2015I’ve got an issue with extracting specific data from one field using charindex. Here’s an example of the dataset:
Cl_nr Var_data
20059942 ?;;300134BL10;?;;;;;;
20059958 ;2698;020225PU20;?;;;;;;
20059975Â Â Â Â ;;100777ST20;?;;;;;;
20059976 ;;;;;;;;;
11001980 ;;051168PU20;?;;;;;;1001980
20034832 ;;060253BO10;?;;;;;;
20055246 ;;1108731;?;?;;;;;
20043656Â Â ;;1022509;?;;;;;;
20059366 ;;1181750;31-12-2015;2;;;;;
20052712Â Â Â Â ;;230626NO10;?;;;;;;
Goal is to get the data after the 2<sup>nd</sup> ;Â until the next ; starts. Ideal would be to catch everything between the 2<sup>nd</sup> ; en 3<sup>rd</sup> ; (number should be 10 characters).
If I try to select this data just using charindex it only goes until it finds the first ; (of course), what’s the best approach in this?
How To Get Full-Text To Not Ingore Special Characters
Jul 20, 2005We just implemented a full-text index on our product master table,however the users are now screaming because they cannot search on someof the special characters that are commonly found in our productdescriptions, specifically the #, %, and period (.)These characters are not in the Noise file, so no luck in justdeleting them from there, but somehow, the full-text is automaticallyignoring those characters, and we would like for the full-text to notignore these characters.Any insight or help would be appreciated.Thanks
View 1 Replies View RelatedTransact SQL :: Replace Special Characters And Space Except Dash
Sep 18, 2015I want to replace special characters and space except '-' Tried with Pathindex and function but not getting expected results.
Ex: Input kjkdjfdf-234#kei$ ewiw
output:Â kjkdjfdf-234keiewiw
Transact SQL :: Special Names For Columns In Cross Tabs?
May 7, 2015While looking forward to design a multi-columnar cross-tab query I am anxious to know if there could be a way to change the default names of the pivot columns? In other words for the query like the following can there be a way to apply anAS type command to reflect some other names, instead of having the four dates in heading? Something like Month_A, Month_B?
SELECT * FROM
(SELECT
X.REP_DT,
X.CUST_ID
AMOUNT_1
FROM
X) P
PIVOT (SUM(AMOUNT_1) FOR REP_DT IN ([2014-12-31], [2015-01-31], [2015-02-28], [2015-03-31])) PVT_01
DB Engine :: Extracting Server Event Log Into Text File
Sep 15, 2015I would like to extract SQL server (event) log information into text file or DB tables so that we can read it with a script everyday.
View 13 Replies View Related