Transact SQL :: Get Everything After And Before Certain Character In Server

Sep 29, 2015

I have a database column that stored the weblinks like the one below :

[URL] .....

I want to extract string  "airfare" which comes  after string "cname= " and before the  string "&ccontent" .This is actually the value for cname. The entire string size can be of variable length. 

View 3 Replies


ADVERTISEMENT

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

Transact SQL :: Invalid Character Validation

Jun 3, 2015

if the variable contains any of the character  that is not present in the  following Below table then it should display "InValid Character" else "valid Character"

DECLARE @MyString VARCHAR(100)
SET @MyString = 'ABC$['
select 
case 

[code]....

View 3 Replies View Related

Transact SQL :: Read Unicode Character Using OPENROWSET

Jun 27, 2012

I am reading text file using OPENROWSET command. The read is successful. But the UNICODE characters in the file is not showing properly in the output.I am using a sql like below:

select
*
FROM
OPENROWSET('MICROSOFT.ACE.OLEDB.12.0','Text;Database=ppa20-igloo-fsMODELSTORECR106_GIM_UKGISourceFilesProcess;HDR=Yes;Format=Delimited(|)',
'SELECT * FROM [RUW_P_Reinsurers_Aviva_and_Others_Map.txt]')

how should I read the unicode characters correctly.

View 8 Replies View Related

Transact SQL :: Inserting Character At The Beginning And End Of A Field

Sep 17, 2015

How to modify values in a SQL Database. In the table PRDDEmpmaster there is a field called email_address, and for those fields that have a value I need to change that value to add { } opening/closing "curly braces" to that field. 

For example,

RPARSONS@YAHOO.COM becomes
{RPARSONS@YAHOO.COM} .  Nulls/empty fields remain as is.    

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

Transact SQL :: Error - Conversion Failed When Converting Date And / Or Time From Character String

Nov 16, 2015

I've imported a CSV file into a table in SQL Server 2012. It's a large file, 140,000+ rows, so I couldn't covert it to Excel first to preserve the date format due to Excel's row limit. In the CSV file, there were 3 column with date data in "31-Aug-09" format, and the import automatically transformed these in "31AUG09" format (varchar(50)) in SQL Server.  Now I need to convert these 3 columns from varchar to datetime so I could work with them in date format.

I've tried several things,e.g,

select
convert(datetime,
right(admdate,4)+left(admdate,2)+substring(admdate,3,3))

or

select
convert(datetime,
substring(admdate,6,4)+substring(admdate,1,2)+substring(admdate,3,3))

but keep getting the same error message (Msg 241) "Conversion failed when converting date and/or time from character string".

View 4 Replies View Related

Read Chinese Character From SQL(SQL Server 2005) Database Table Column And Display Chinese Character

Feb 1, 2008

Hi!

I have a table like this below and it doesn't only contain English Names but it also contain Chinese Name.
CREATE TABLE Names
(FirstName NVARCHAR (50),
LastName NVARCHAR (50));
I tried to view the column using SQL Query Analyzer, It didn't display Chinese Character.
I know that SQL Server 2005 is using UCS-2 Encoding and Chinese Character uses Double Byte Character Set (DBCS) Encoding.
I want to read the FirstName and LastName columns and display in Window Form Data Grid and ASP.NET Grid View.
I tried to use this code below and it didn't work. It convert some of the English Name to Chinese Character and it display the chinese character and some still in the original unreadable characters.
Does anybody know how to read those character from SQL Table and display the correct Chinese Character without converting the English Name into Chinese also?
Thanks

int codePage = 950;
StringBuilder message = new StringBuilder();
Encoding targetEncoding = Encoding.GetEncoding(codePage);
byte[] encodedChars= targetEncoding.GetBytes(str);
.
message.AppendLine("Byte representation of '" + str + "' in Code Page '" + codePage + "':");
for (int i = 0; i < encodedChars.Length; i++)
{
message.Append("Byte " + i + ": " + encodedChars);
}

message.AppendLine(" RESULT : " + System.Text.Encoding.Unicode.GetString(encodedChars));
Console.Writeline(message.ToString());

View 1 Replies View Related

Transact SQL :: Due Date - Conversion Failed When Converting Date And / Or Time From Character String

Nov 16, 2015

SELECT * ,[Due]
  FROM [Events]
 Where Due >= getdate() +90

This returns the error: Conversion failed when converting date and/or time from character string

Why would this be? How to cast or convert this so that it will work? 

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

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

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

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

SQL Server Equivalent Of Character Set

Apr 30, 2007

Hello MSSQL professionals,

Please tell me the equavalent of the below MySQL query in SQL Server.

create table temp1(variable1 varchar(24) character set latin1 not null default '')
Immediate help will be highly appreciated

View 2 Replies View Related

Character Sets In SQL Server

Jul 23, 2005

Hello!My client has a need to be able to store Japanese characters in theirPeopleSoft database. So we need to change the character set from fromLatin1_General (1252) to Japanese character set (932) on SQL Server2000 Enterprise. I have 2 questions:1) I would like to know if in SQL Server, the character set is machinespecific or is it defined at the database instance level?2) Can multiple installs of SQL Server co-exist on a single server withdifferent default character sets?ThanksVishal

View 1 Replies View Related

Sql Server Instance Name Should Not Contain '-' Character

Jun 5, 2007

Hi All,



I am using System:ata::Common:bDataAdapter::Fill method in my Visual Studio 2005 project to achieve copy database from an existing database on server. - Server name contains '-' character. I am getting exception on this method about 'Incorrect syntax near '-' ". Does it really matter with server instance name containing '-' character?



Please help.



Thanks,

Anni

View 6 Replies View Related

What Is This Box Type Character For A Blank Space In SQL Server?

Mar 8, 2007

Hi,
I am using DTS for importing a table from FileMaker Pro (a database application) to SQL Server 2000. The problem is that everything gets imported correctly but in some values, it shows a box type character for a blank space. It does not happen for all the blank spaces. Because of this, that value is cannot be checked in the WHERE clause until I use a LIKE and % for that blank space.
I copied the value and pasted in Notepad, it only shows a blank space. What is this character?
Can someone help...
Thanks,
bullpit 

View 4 Replies View Related

How Do I Escape A New Line Character Thats Been Stored In Sql Server

May 30, 2008

Not sure if this is the right forums to ask this question..
I have a website in which i have a text box that has multiline = true... So suppose if if the user enter absbsbcjd and then hits enter and then again enter jfkdjf in my database i been stored as abscbabc twoboxes (new line) and then jfdkf and then sometime later i am taking that plan name and sending it as a subject to an email but since it has a new line character in that field.. i get an error..
 
so how can escape the new line character..
thanks
Karen

View 6 Replies View Related

How To Store Large Character Data In MS SQL Server ???

Sep 11, 1998

I am developing a simple ASP based form, that stores user info in MS SQL server. I have created a table in the SQL server to store the data and defined the body field with this line: `body char(255)`. The problem is that if the user inputs a string longer then 255 characters it gets choped off. How would you suggest solving this problem? Should I use `text` datatype instead?? Any comments are very appreciated !!!

View 2 Replies View Related

SQL Server 2012 :: Character Value Count From Dataset

Oct 31, 2014

Consider following script

create table data_set (id int primary key, col1 varchar(10))
go
insert into data_set values (1,'a'), (2,'b'),(3,'c'),(4,'d'),(5,'a'),(6,'b'),(7,'e'),(8,'f'),(9,'a'),(10,'a')
select * from data_set

I tried this below

Declare @child_ids int
@col_val varchar
@count int

select @child_ids, @col_val, @count, count(col1) as records from data_set
group by col1
order by col1

But I want my output like below .....

View 2 Replies View Related

SQL Server 2008 :: Sum Result Type Character?

Feb 1, 2015

i need result from '(1000/3)+(15/100)'. try exec() by failed.

View 5 Replies View Related

SQL Server 2012 :: Tab Character In Query Results

Jul 9, 2015

I am getting a tab character at the end of my query fields, been trying various things to fix, such as using the replace function below but still i get the tabs!

select CAST(REPLACE(NAMEALIAS,CHAR(9),'')AS CHAR(40)) + ',' as PRODNAME,
CAST(REPLACE(ISNULL(GLOBALTRADEITEMNUMBER,0),CHAR(9),'')AS CHAR(18))as EANNO,LTRIM(cast(ISNULL(GLOBALTRADEITEMNUMBER,0) as char(18))) as KONSEAN
,LTRIM(CAST(I.ITEMID AS CHAR(8))) AS PRODCODE,'00' from INVENTTABLE I
LEFT JOIN INVENTITEMGROUPITEM IG ON I.ITEMID = IG.ITEMID

[Code] ....

Results:
Gießanzeiger groß ,04260376730475 04260376730475 730475 00
Gießanzeiger klein , 04260376730352 04260376730352 730468 00
Gießanzeiger klein , 04260376730468 04260376730468 730468 00

View 9 Replies View Related

SQL XML :: Server Is Removing First Character Of Values If It Is A Space

Jun 3, 2015

I'm creating a table using a XML string and a stored procedure to the SQL Server. I'm having problems in just one field, it is a nvarchar(10) type. This field's string XML Schema is also nvharchar(10) type. It is called CodCanalPrice.

Today, the only values this field can assume are AA or BB or CC, (...), ZZ or (space)A, (space)B, (...), (space)C. Note that (space) is actually a space character. They all have 2 characters.

The XML data string of one data row is showed below:

<row id="1" (...many fields...) CodCanalPrice=" A" />

When the statement is executed, SQL server removes the first space (so in this case it is updated to A without space in the table). The others values that don't have an space before like AA or BB are updated fine. All others fields are also updated correctly.

So far I tried to replace the first space for   but SQL Server doesn't accept this tag.

View 13 Replies View Related

SQL Server 2012 :: Removing Character (Letters Only) From A String

Dec 19, 2014

I need to build a query, or use a function to removing alfabetic Character from a string.

My string is
WI14000323-0003

The value i want to keep is : 14000323-0003.

I try using this SELECT:

SELECT STUFF(Upper(z.lote), PATINDEX('%[A-Z]%', Upper(z.lote)), 2, '') from mytable

but in this case i have only 2 letters WI, but in the future i dont know if is only 2 letters, 1 letter or more, then is not what i pretend.

View 2 Replies View Related

SQL Server 2008 :: Cannot Drop Table With Non-Printable Character On Its Name

Sep 21, 2015

I have a legacy table which name contains non-printable characters (CHAR(31), to be more specific). The non-printable character is beside a underscore, and I've discovered that the shortcut CTRL+SHIFT+_ shoots the CHAR(31) character (which means "US" - Unit Separator). The previous developer should've hit by mistake this combination, and created the table with this weird character on it.

When we issue a SELECT command against the table, it returns results. But when we try to issue any DDL to it (DROP, sp_rename, etc), it simply doesn't work.

Examples:
DROP Table_Name; raises "Msg 15225 - No item by the name of 'Table_Name' could be found in the current database 'MyDB', given that @itemtype was input as '(null)'".
sp_rename 'Table_Name', 'NewTableName'; raises "Msg 102 - Incorrect syntax near '_Name'".

I already duplicated the table with the correct name, and have corrected it on the referenced objects (SP's, Views etc). The remaining step is just dropping it from the database. when we copy+paste from SQL Server to Notepad++, it shows the hidden chacacter ("US") on the middle of the table name, beside the underscore.

View 9 Replies View Related

SQL Server 2012 :: Removing Duplicate Character From String

Oct 18, 2015

How to write a function to remove duplicated characters only if they come in sequence.

Examples

darrk should return dark

eeagle should return eagle

redd should return red

corner should corner as it is as the r's are not in sequence.

View 9 Replies View Related

Invalid Character Value For Cast Specification: SQL Server 2000

Jul 20, 2005

Hi,I used SQL Server 2000 Personal Edition. I created a stored procedurehaving input parameters as smallint,tinyint,char,varchar andsmalldatetime. When I executed it by Query Analyzer, it's ok. But whenI executed it by ASP code that used ADODB.command, it showed error:Invalid character value for cast specification .Though SQL Server debugger, it showed:[Microsoft][ODBC SQL Server Driver]Invalid character value for castspecificationWhat did I do wrong? How do I fix this problem?TIAKrungPS: One of input parameters was smalldatetime, but I casting it in ASPas date as:Const adDate=7datetxt='2004/6/28'cmd.Parameters.Append cmd.CreateParameter("CoopId",adChar,adParamInput, 8, coopid)cmd.Parameters.Append cmd.CreateParameter("Addr",adVarChar,adParamInput, 100, addr)...cmd.Parameters.Append cmd.CreateParameter("Cdate",adDate,adParamInput, , datetxt)....cmd.execute

View 3 Replies View Related

How To Set Up SQL Server To Do Full Text Search Against Double Byte Character

Jan 10, 2001

I had a situation that required me to set up SQL Server to do full text search against both English content and Chinese content. I am not sure if it's achievable in SQL server environment. Any help is appreciated.

View 2 Replies View Related

SQL Server 2008 :: Finding Multiple Character Occurrence In String

Mar 24, 2015

I am trying to figure out how I can find the names of people who have a double occurrence or more of characters either in their first name or last name.

This is for a data cleanup.

CREATE TABLE #Names ( FIRSTNAME VARCHAR (20), LASTNAME VARCHAR (20))
INSERT INTO #Names VALUES
('Thomas', 'Hohner')
,('Mike', 'SSmith')
,('TtTony', 'Hawk')
,('Jeff', 'Smith')
,('ZZSara', 'Jones')
,('Luke', 'HendersonZZ')
,('Lily', 'PPadZZZ')

SELECT * FROM #Names
DROP TABLE #Names

View 9 Replies View Related

SQL Server 2012 :: CSV Import With Non-character Data (Date And Decimal)

Mar 29, 2015

I'm using OpenRowSet to import about 30 columns from a csv file with 190 columns with a format file. Ultimately, I want to put this in an SSIS Package. I am receiving the following error when trying to import date and decimal info.

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 990, column 64 (TOTALSALES). There are several similar errors. I looked at this line and it is 17873.34 so I am not seeing the problem. Every value in the column is either 0 or a 2 digit decimal value. If I change the SQL Column and format file to to NVARCHAR, it imports fine.

The existing format file and SQL Column looks as follows. There are multiple errors referring to different columns and all of them seem to be valid decimals. I am having the same issue with date fields that exist in the csv as 20130521. If I bring it in as text, it is fine.

<FIELD COLLATION="SQL_Latin1_General_CP1_CI_AS" MAX_LENGTH="12" TERMINATOR="," xsi:type="CharTerm" ID="64"/>
<COLUMN xsi:type="SQLDECIMAL" SCALE="2" PRECISION="15" NAME="TOTALSALES" SOURCE="64"/>

The SQL Column is defined as Decimal ((15,2), NULL))

I created a small csv file with representative decimal, date, integer and NVarchar fields and it imports into SQL fine as decimal and date info. The SQL Query used is pretty simple. Ultimately, I am planning to create a package that imports this data and joins to a production table based on values in the csv file. It will either update existing values in a Production Table or insert New Values

INSERT INTO Import.dbo.test1
SELECT *
FROM OPENROWSET(BULK 'C:ShareImport.csv',
FirstRow=2,
FORMATFILE='C:ShareImport.xml'
) AS t1;

I am assuming there is bad data in the csv file but I'm not sure how to identify it as my test file seems to bring in date values with a format of 20140923 and 2 digit decimal values and that is what exists in the line numbers being referenced. I've not used OpenRow Set for this purpose before. The only workaround I've found is to bring it all in as text and create additional fields so I can cast or convert the date values which I'd rather not do as this process seems to work in my small sample file.

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

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

SQL Server 2012 :: Replacing Recurring Characters In A String With Single Character

Jan 20, 2014

I have a problem where I want to write a function to remove recurring characters from a string and replace them with a single same character.

For instance I have the string '12333345566689' and the result should be '12345689'. In Oracle I could do this with "regexp_replace('12333345566689', '(.)1+', '1')", but in T-SQL the only solution I could think of is something like this:

DECLARE @code NVARCHAR(255)
SET @code = '12333345566689';
SET @code = REPLACE(REPLACE(REPLACE(@Code, '1', '~1'), '1~', ''), '~1', '1');

and repeat this for 2 - 9. But I'm sure there is a more elegant version for this in SQL Server 2012.

View 9 Replies View Related







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