Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

PATINDEX And '[' Character

Hi all

Using SQL Server 2000 SP3 on WIn2K Pro with SP2.

When I do this


I get a result of 0, when I would expect a result of 4.

This works as I expect:


I must be missing something really obvious here Any help would be

View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Patindex To Find Special Character And Next 6 Digits
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


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



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_pick_ticket.carrier_id AS Carrier,
P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id AS Supplier,


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

'' END) AS alternate_address,

'Y' AS QVN, '' AS failureaddress,


------------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 = 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_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 Replies !   View Related
Good morning.

I was wondering if someone would point out my flaw. I am attempting to get information separated by spaces, placed into separate columns. I tried without success several times yesterday to get what I desired and was not able to get it done. I am fairly new at developing SQL code and am low on the SQL coding totem pole of experience but would like your help.

Here is what I have so far.

PATINDEX('% %', c1))) SSI_House_Number,
PATINDEX('% %', c1)+1,
LEN(c1)-PATINDEX('% %', c1)+1)) SSI_Directional
c2 = 4254

My problem arises when I, in the second column, attempt to get only the information BETWEEN the first space: PATINDEX('% %', c1)+1 and the second space. I am coming up short and would like someone to point out my error or assist me in getting the query pointed in the right direction.


View Replies !   View Related
Hello everyone:

how do i find a single quote ['] with the patindex function.

SELECT 'a company's name'



View Replies !   View Related
Question On Using Patindex
Hello,I am trying to update a column in a table with an 8 digit string.The 8 digit number is obtained from another column which is a commentsfield.e.g.Comments Field :1) RD-V0216970 RQST NON SMKING KING Unknown # : 223389212) Received verbal authorization Authorization # 0403453) international plaza, singapore # 96722540The code that I am using isUPDATE U SET U.NUM =CASEWHEN U.BOOKED_COMMENTS_TXT LIKE('%[0123456789][0123456789][0123456789][0123456789][0123456789][0123456789]%')THENSUBSTRING(U.BOOKED_COMMENTS_TXT,PATINDEX('%[0123456789][0123456789][0123456789][0123456789][0123456789][0123456789]%',U.BOOKED_COMMENTS_TXT), 8)ELSE NULLENDFROM UNKNOWN1_RESERVATIONS UHere's what my result set looks like1)V02169702)0403453)96722540But this is how I want my result set to look like1)223389212)null3)96722540What I need is a way to restrict the search criteria to exactly 8numeric digits. Any suggestions will be helpful.Thanks in advance

View Replies !   View Related
PatIndex(..,...) How To Get Next Pattern?
Hi Guys!

I'm trying to split down a column of values down into further columns.. I have managed to do this fine using substring and patindex

SQL Code:

- SQL Code

declare @i int
set @i = 0
declare @String varchar(40)
declare @Pi int
--put all our data into a temp table - so we don't query the auditlogs constantly!

FROM AuditLogs
tablename = 'EMD'
and KeyInfo LIke 'EMID=76676%'

Select @string = LEN(cast(keyinfo as varchar(8000))) From #Audit

while @i < @string
SELECT substring(keyinfo,@i,patIndex('%;%',keyinfo)) From #Audit
Set @i = @i + 1

drop table #Audit

DECLARE @i int SET @i = 0DECLARE @String varchar(40)DECLARE @Pi int--put all our data into a temp table - so we don't query the auditlogs constantly! SELECT * INTO #Audit FROM AuditLogs WHERE     tablename = 'EMD'     AND KeyInfo LIKE 'EMID=76676%'   SELECT @string =  LEN(CAST(keyinfo AS varchar(8000))) FROM #Audit  WHILE @i < @stringBEGINSELECT SUBSTRING(keyinfo,@i,patIndex('%;%',keyinfo)) FROM #AuditSET @i = @i + 1END DROP TABLE #Audit  

But as you can see this only increaments in single values and only gets the first occurance of the pattern..
when the data is such as...


Please note I have changed the column names, they're not actually named that way!!


View Replies !   View Related
Working With Patindex
i have one datatable with some file's details like id, name, createdate. i wrote one storedprocedure to search in that table by filename using wildcard character. its also working perfectly. But the problem is i want to arrange the results in ascending order depended by matching level. If I search for 'file123' it should give results in one order by matching level, means - lets think the results are 'fil23', file123', 'file', 'file1'. the results should be in order like 'file123', 'fil23', 'file1', 'file'. the results of the sp which i used, are order by datecreated. Please help me to do this.

the storeprocedure i used -
"CREATE PROCEDURE [dbo].[SP_DYN_SearchinFiles_DynByWildcardsADMIN] @searchparam nvarchar(50)
from Files_Dyn where PATINDEX('%' + @SearchParam + '%', OriginalName) > 0


View Replies !   View Related
Problem With PATINDEX Function
Hi all,
My database is not case-sensitive, but I want output like...
SELECT patindex('%[A-Z]%','gaurang Ahmedabad')
The output should be first occurrence of uppercase A to Z, so output should be 9 it should not be 1.
Above query is giving output as 1 bcoz the 1st character in the expression is 'g' and it is in A to Z, but this is not capital 'G'. The 1st capital letter in the expression is 'A' (9th character in the expression).
Is there anyway to achieve this using PATINDEX? or Is there any other way to achieve this?
Gaurang Majithiya

View Replies !   View Related
How To Search Using PATINDEX Function...Plz Help

How to search the last occurance of one String in to another string..Using function PATINDEX() in a column of data type Text..

plz Give some solution i really need it ...

Thanks Yogesh

View Replies !   View Related
Question Using Patindex In Sql Server
Alright, I am trying to write a query that will select a column.. then search through the column and return me the fields with no underscores in them.

i.e we have fields that are like ABCD and ABCD_1. I want to basically return all of them up to the underscore.

so i am trying to use a substring combined with patindex. Here is my statement for patindex. patindex('%_%', short_name)

however, i cannot get patindex to ignore the fact that _ is a wildcard and treat it as character. Ive tried ('%[_]%', short_name) and ive tried ('%!_%', short_name) escape '!' and i just keep getting errors... If someone could help me out, that would be most appreciated. thank you.

View Replies !   View Related
Better PATINDEX ( '%pattern%' , Expression )
Hello, i am customizing an application that uses PATINDEX ( '%pattern%' , expression ) in its stored proceadure for searching. the problem with that is that "Work Boot" will return My Work Boot, but not My New Work Boot, I am wondering if there is another TSQL function that would do a PATINDex for each term of the query... soth that "work boot" does a search on "Work" and "Boot"
Thanks in advance for the help.

View Replies !   View Related
Hey all,
I have this query... it's funny because if you separate out each part it seems to work, but together it gives me the "The text, ntext, and image data types cannot be compared or sorted..." error. I don't really see how it applies here, because PATINDEX can work on text, varchar, and their variations. Anyways, here's the query:


FROM VisitorTrackingSources
WHERE PATINDEX(ReferralPattern, RefererURL) > 0
ORDER BY Priority DESC) AS Expr1
FROM VisitorTracking

ReferralPattern is nvarchar, RefererURL is ntext, Priority is an int. It works if I replace RefererURL with a string literal. Help?

View Replies !   View Related
PATINDEX / LIKE - How To Do A Regular Expression In One Of These?
I have a table which stores a big chunk of html text, which I have to search for and replace some strings.
How can I write a query that makes use of regular expressions to do the following:
Below is a block of example text
" src="http://aaaa/bbbb/cccc.nsf/blind%2010x10.gif?OpenImageResource" width=10> <img height=10 alt=""  src="http://aaaa/bbbb/cccc.nsf/blind%2010x10.gif?OpenImageResource" width=10><img height=11 alt=""
http://aaaa/bbbb/cccc.nsf/blablablabla?OpenDocument src="http://aaaa/bbbb/cccc.ns http://aaaa/bbbb/cccc.nsf/d8d4f0dfa09ba71142256cc50040ee51/ee9b4493dac727be422571c30027df61? OpenDocument" target=_self>

Now I want to search and replace the string that is highlighted in red with something like "FOUND"
The below string is what I want to search for - I will used PATINDEX on the string below to find the start index of the string in the body, and then I will add on 114 to the when doing a replace:
'%http://aaaa/bbbb/cccc.nsf/%some guid%/%some guid%?OpenDocument%'

For example

             PATINDEX(''%http://aaaa/bbbb/cccc.nsf/%some guid%/%some guid%?OpenDocument%', TheField),


what would the regular expression be and how does one type it in the query?
I tried something like the below but it does not work, please advise .....
[a-z][0-9]{32} %/% [a-z][0-9]{32}

View Replies !   View Related
Using PatIndex To Find All Numeric Values
I forget. How can PATINDEX be used to find column values where the data is all numeric? I've tried, the following:

select distinct acct from tbl_CYProcessedSales

where PatIndex('%[0-9]%',Acct) > 0

order by acct

Acct is varchar(8). What am I doing wrong?
Thank you for your help!

View Replies !   View Related
Select Part Of Character String Based On A Character
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 Replies !   View Related
Validating Inputs And Finding Patterns With PatIndex
---Checks that input only contains numbers
if PatIndex('%[^0-9]%','11') > 0
Print 'Not all numbers'
Print 'All numbers'

---Checks that input only contains letters
if PatIndex('%[^a-z]%','aaaaa') > 0
Print 'Not all letters'
Print 'All letters'

--Checking for mixed input
If PatIndex('%[^0-9][0-9]%','abc') > 0
Print 'Alpha numeric data'
Print 'Either all numbers or all letters'

--Checks that value must start with a letter and a number
If PatIndex('[^0-9][0-9]%','A1anamwar11') > 0
Print 'Starts with a letter and a number'
Print 'Does not start with a letter and a number'

--Checks that value must End with a letter and a number
If PatIndex('%[^0-9][0-9]','A1anamwar11a1') > 0
Print 'Ends with a letter and a number'
Print 'Does not End with a letter and a number'

--Checks that value must Start with a letter and Ends with a number
If PatIndex('[^0-9]%[0-9]','namwar1') > 0
Print 'Starts with a letter and ends with a number'
Print 'Does not start with a letter and ends with a number'

<Link removed by georgev>

View Replies !   View Related
Need Alternative To PATINDEX In Derived Column Transform
I understand that it is not possible to use PATINDEX in a Derived Column transform. I'm trying to eliminate leading zeros from a column where the string is always 14 characters long, but the first non zero character could occur any number of characters from the left of the string. The following achieves what I need:

substring([Account No w/0s], patindex('%[^0]%', [Account No w/0s]), 14) AS ExtractedAcctNo

Does anyone happen to know how I might express this differently in a Derived Column transform? Should I consider a calling a function through an OLE DB Command transform on each row instead? Maybe I should have a SQL Task that runs an UPDATE statement against the column.
Thank you for your help!

View Replies !   View Related
Substring And Patindex Having An User Defined Function
Hi All,
Can we use the SUBSTRING or PATINDEX function with a User Defined Function as one of the parameters? Something like this:

Code Block
Select SUBSTRING(Select * from DB.dbo.Function('Parameter'), PATINDEX('%\%', Select * from DB.dbo.Function('Parameter'), 8)


View Replies !   View Related
Problem With PATINDEX Function For Case-sensitive Information
My database is not case-sensitive, but I want output like...
SELECT patindex('%[A-Z]%','gaurang Ahmedabad')
The output should be first occurrence of uppercase A to Z, so output should be 9 it should not be 1.
Above query is giving output as 1 bcoz the 1st character in the expression is 'g' and it is in A to Z, but this is not capital 'G'. The 1st capital letter in the expression is 'A' (9th character in the expression).
Is there anyway to achieve this using PATINDEX? or Is there any other way to achieve this?
Gaurang Majithiya

View Replies !   View Related
XML Parsing: Line 26, Character 5, Illegal Xml Character

I have a Table with 5 million rows and a column that is "Text" and some of the column lengths are 690,000 characters. Most of the data in this one column is XML data and it is taking a long time to find and replace values in the XML nodes.  In another post it was recommended that we just change the column to XML and then do an update to the values.
When I try this in a test environment with a small sample of data it works fine and I change it from Text to XML, update the values, and then change it to VARCHAR(MAX) and then back to Text (to avoid having to mess with code releases, etc.)
In a production environment the switch from Text to XML fails after 14 hours saying that "XML parsing: line 26, character 5, illegal xml character" which I have to assume that there is data in at least one of the rows that is not a valid XML character.
I think I read that when changing the column type to XML it has to go through all of the rows and validate that there is valid XML in the column.
Is there a way to turn this validation off?  (like NOCHECK or something)
Is there an easy way to search through these column values for the illegal characters?
Does anyone know which specific characters are illegal?
Keep in mind that this is a very wide column in a big table where just this one table makes up about 400GB or more of this DB size.
Cursoring through the rows and leaving the column as text, the script below takes 3 seconds per row to grab the data for 1 row, convert it to xml, replace 7 of the values and then convert it back.  (I tried a "while loop" with no performace gain as well)
3 seconds times 5 million rows would take about 600 hours in a production environment where I would probably not be able to run this during the day.
ANY help would be much appreciated!!!

  Select @snapShot = CONVERT(xml, SnapshotText) from Audit_ProductInstances where Audit_ProductInstanceID = @AuditProdID

  Set @snapShot.modify('replace value of (/WorksheetData/MergeFields/MergeFieldBase[Name="[LogoLarge]"]/Value/text())[1]
        with ""')
  Set @snapShot.modify('replace value of (/WorksheetData/MergeFields/MergeFieldBase[Name="[LogoExtraSmall]"]/Value/text())[1]
        with ""')
  Set @snapShot.modify('replace value of (/WorksheetData/MergeFields/MergeFieldBase[Name="[LogoMedium]"]/Value/text())[1]
        with ""')
  Set @snapShot.modify('replace value of (/WorksheetData/MergeFields/MergeFieldBase[Name="[LogoMediumLarge]"]/Value/text())[1]
        with ""')
  Set @snapShot.modify('replace value of (/WorksheetData/MergeFields/MergeFieldBase[Name="[LogoSmall]"]/Value/text())[1]
        with ""')
  Set @snapShot.modify('replace value of (/WorksheetData/MergeFields/MergeFieldBase[Name="[Seal]"]/Value/text())[1]
        with ""')
  Update Audit_ProductInstances set SnapShotText = Convert(varchar(max), @snapShot), FixedFlag = 1, SQLUser = 'FixedSnapshot'
  where Audit_ProductInstanceID = @AuditProdID


View Replies !   View Related
Read Chinese Character From SQL(SQL Server 2005) Database Table Column And Display Chinese Character

I have a table like this below and it doesn't only contain English Names but it also contain Chinese Name.
(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?
        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));

View Replies !   View Related
Tab Character
what nchar() value equals the tab character. I need to replace it in astring.

View Replies !   View Related
Character Set
Anytime I BCP data from a table containing characters like ö it gets muddled up when I look at it in the text file. The code page of the NT OS is 850 and the character is included in it. Why is the character changed during the BCP ?


View Replies !   View Related
Character 0
How can i add character 0 to the database??

i have a string of characters and one of them is 0 (its a license file)

i would like to save it to the database. but i get unclosed character string.

Hope its clear.


its a new database so you can also tellme wich datatype i need.

View Replies !   View Related
Using BETWEEN With Only One Character
Pls, what d this query return if applied on char type (string type)
select * from myTable where myNameColumn Between 'B' AND 'E'

would return all the names that start with : B, C, D, E
like these:

Bob, Chris, Edward, David

but not :

Marry, Jean....
or how does it work exactly when applied on string type but only putting one character in the search.

View Replies !   View Related
The ' Character
Hello all.

Ok this is going to be a little hard to describe but here goes

I have a table within which one column is used to store a SQL select statement as a string. This means that the whole select statement has to be enclosed within two ' characters. The problem is that the SQL statement itself contains these characters. The specific SQL statement i wish to save as a string is:

'<A HREF=DisplayOnlyDiscipline?SESSION_ID=' + :SESSION_ID +
'&DISP_REF=' + cast(DISP_REF as varchar) +
'&EDIT_REF=' + :EDIT_REF + '>' + cast(DISP_REF as varchar) + '</A>' as Reference,
V.DESCRIPTION as Discipline_Stage,
left outer join
order by

Can anyone suggest how i get round this?

thanks for reading peeps

View Replies !   View Related
What Is This Character/why Am I Seeing It?
I'm using MS SQL intelligence studio's SSIS import wizard to import some Excel files into SQL tables and for certain fields I'm getting this strange character instead of NULL. Does anyone know what this character is, how to avoid it, or a SQL query that will get rid of it? I can't even copy it to my clipboard.

View Replies !   View Related
Tab Character
I have a textbox in my rdlc report.  I can do this to get newlines:

="some text" & Environment.NewLine & "more text"

but cannot figure out how to get a tab character.  If I do this:

=" some text"

It literllay prints the slash t not a tab character.

I have tried chr(9) and ControlChars.Tab

Any other ideas?

As a hack I tried entering 5 spaces. So it looked like this:

="     Some text" & Environment.NewLine & "     More text"

When the report generated the spaces before "Some Text" were missing as if trimmed, but the spaces before "More Text" did show up.

So it seems that even using sapces will not work.  Any ideas?

View Replies !   View Related
Get Certain Character

I have got a column having data like
RT 12.5R20 (P) OL

I want only need the value before '('. So, the result will be RT 12.5R20 and RT 12.5R244
Since the length of the character will change. Is there any funtions in T-SQL that I can use to achieve this?

View Replies !   View Related
Replace, The ' Character, And Yea...
I have the following:----------------- WHILE PATINDEX('%,%',@Columns)<> 0 BEGINSELECT @Separator_position = PATINDEX('%,%',@Columns)SELECT @array_Value = LEFT(@Columns, @separator_position - 1)SET @FieldTypeID = (SELECT FieldTypeID FROM [Form].[Fields] WHERE FieldID = (CAST(@array_Value AS INT)))SET @FieldName = (SELECT [Name] FROM [Form].[Fields] WHERE FieldID = @array_Value)print 'arry value' + CONVERT(VarChar(500), @array_value)print 'FieldTypeID: ' + CONVERT(VARCHAR(500), @FieldTypeID)PRINT 'FieldName: ' + @FieldNameBEGINIF @FieldTypeID = 1 OR @FieldTypeID = 2 OR @FieldTypeID = 3 OR @FieldTypeID = 9 OR @FieldTypeID = 10 OR @FieldTypeID = 7BEGINSET @InnerItemSelect = ' (SELECT ISNULL(CONVERT(VARCHAR(MAX),[Value]),'''') FROM [Item].[ItemDetailFieldRecords] IDFR WHERE IDFR.ItemDetailID = ID.ItemDetailID AND IDFR.FieldID = ' + @array_Value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT ISNULL(CONVERT(VARCHAR(MAX),[Value]),'''') FROM [Item].[TaskFieldRecords] TFR WHERE TFR.TaskID = T.TaskID AND TFR.FieldID = ' + @array_Value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FieldTypeID = 4 OR @FieldTypeID = 8 --DropDownList/RadioButtonlistBEGINSET @InnerItemSelect = ' (SELECT [Value] FROM [Form].[FieldListValues] FFLV INNER JOIN [Item].[ItemDetailFieldListRecords] IDFLR ON FFLV.FieldListValueID = IDFLR.FieldListValueID WHERE IDFLR.ItemDetailID = ID.ItemDetailID AND FFLV.FIeldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT [Value] FROM [Form].[FieldListValues] FFLV INNER JOIN [Item].[TaskFieldListRecords] TFLR ON FFLV.FieldListValueID = TFLR.FieldListValueID WHERE TFLR.TaskID = T.TaskID AND FFLV.FIeldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FieldTypeiD = 5 --CascadingBEGINSET @InnerItemSelect = ' (SELECT [FCV].[Value] FROM [Form].[FieldCascadingValues] FCV INNER JOIN [Form].[FieldCascadingLookUpTables] LT ON FCV.FIeldCascadingLookupTableID = LT.FieldCascadingLookupTableID INNER JOIN [Item].[ItemDetailFieldCascadingRecords] IDFCR ON IDFCR.FieldCascadingValueID = FCV.FieldCascadingValueID WHERE IDFCR.ItemDetailID = ID.ItemDetailID AND LT.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT [FCV].[Value] FROM [Form].[FieldCascadingValues] FCV INNER JOIN [Form].[FieldCascadingLookUpTables] LT ON FCV.FIeldCascadingLookupTableID = LT.FieldCascadingLookupTableID INNER JOIN [Item].[TaskFieldCascadingRecords] TFCR ON TFCR.FieldCascadingValueID = FCV.FieldCascadingValueID WHERE TFCR.TaskID = T.TaskID AND LT.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FieldTypeiD = 6 --ListBoxBEGINSET @InnerItemSelect = ' (SELECT i.[CSV] FROM @ItemDetailLV i WHERE i.ID = ID.ItemDetailID AND i.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT it.[CSV] FROM @TaskLV it WHERE it.ID = T.TaskID AND it.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FieldTypeID = 11 --UsersBEGINSET @InnerItemSelect = ' (SELECT SU.[UserID] FROM [Security].[Users] SU INNER JOIN [Item].[ItemDetailUserRecords] IDUR ON SU.UserID = IDUR.UserID WHERE IDUR.ItemDetailID = ID.ItemDetailID AND IDUR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT SU.[UserID] FROM [Security].[Users] SU INNER JOIN [Item].[TaskUserRecords] TUR ON SU.UserID = TUR.UserID WHERE TUR.TaskID = T.TaskID AND TUR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FIelDTypeID = 12 --GroupBEGINSET @InnerItemSelect = ' (SELECT SG.[GroupID] FROM [Security].[Groups] SG INNER JOIN [Item].[ItemDetailGroupRecords] IDGR ON SG.GroupID = IDGR.GroupID WHERE IDGR.ItemDetailID = ID.ItemDetailID AND IDGR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT SG.[GroupID] FROM [Security].[Groups] SG INNER JOIN [Item].[TaskGroupRecords] TGR ON SG.GroupID = TGR.GroupID WHERE TGR.TaskID = T.TaskID AND TGR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDENDPRINT 'Inner Item Select:' + @InnerItemSelectPRINT 'Inner Task Select:' + @InnerTaskSelectSET @IDSelect = @IDSelect + @InnerItemSelect + ', 'SET @TSelect = @TSelect + @InnerTaskSelect + ', 'SELECT @Columns = STUFF(@Columns, 1, @separator_position, '')END   --------------- That is only part of a large query that writs a SQL Query to a column in a Database. That Query (in the column) is just ran normally so I don't need to compile it each time I want to run it.THe problem I have is  @FieldName might be:     ryan's field. That apostrophe is killing me because the SQL keeps it as ryan's field, not ryan''s field(note the 2 apostrophes).  I cannot do: REPLACE(@FieldName, ''', '''') because it's not closing the apostrophes. Is there an escape character that I can use to say only one: ' ?Would the only solution be to put: ryan''s field into the Database, and just format it properly on the output?  Thanks. 

View Replies !   View Related
Chinese Character
I wanted to stored some chinese character as well as displaying it in browser. Do i need to set my datatype to nchar ?
I have tried putting datatype to nchar. When i paste the chinese character directly to DB. It works.But when i try using INSERT statement, it appear as "????"And when i try display it in browser (charset =UTF-8), it also display as "????" Any advice on solving this?Thanks

View Replies !   View Related
Max Character In Field
In a SQL Server Db, what is the maximum amount of data a field can hold?

I am using right now nvarchar with a length of 4000.

Is there another setting to allow even more in a field?

Thanks all,


View Replies !   View Related
Sorting - Character After 'Z'?
I'm using collation SQL_Latin1_General_CP1_CI_AS and I need to sort avarchar field. There are some elements, however, that I want to comeAFTER any alphabetic characters. Is there any character that sortsafter "Z"? In normal ASCII, there are various characters that wouldsort after "Z" (e.g. "~"), but I can't seem to figure out what the casewould be in the SQL_Latin1_General_CP1_CI_AS collation. Any ideas?thanks,Duy Lam

View Replies !   View Related
Character Set Support
I'm looking at an MS SQL server database and it stores city namesacross the world. All aarabic , chinese etc names are converted to the latin alphabet.I feel it is probably critical we support accents as a means todistinguish between cities that would be converted to the same Latinalphabet representation otherwise and as a matter of culturalpoliteness.Now ...... how do we do this?Use varchar? which character code page do we use?Use nvarchar? I heard a rumour that SQL server pads unused characterswith blanks and hence will use space unnecessarily.The data will be presented on sheets and emailed around the world - itwill also appear on a website.Any advice greatly appreciated - thanksOliver

View Replies !   View Related
TDS And Character Encoding
I've seen a dump of the TDS traffic going from my webserver to the SQLServer database and it seems encoded in Unicode (it has two bytes perchar). Seems it would have a huge impact on performance if ittravelled in one byte. Why might this be?rj

View Replies !   View Related
Text With ' Character
Hi,How do I insert text that contains the ' character into a text column?I'm getting an Incorrect syntax error.

View Replies !   View Related
Special Character
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 Replies !   View Related
Override SQL Character
I'm trying to search for all records that contain a quotation character in the database. These records were migrated from a mainframe system.

I use the following command:

The results are:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the character string ' order by cliLastName, cliFirstName, cliBirthName'.

/ladds/lib/getrecordset.asp, line 6

Is there a way to override the quotation character temporarily?

Thanks ...

View Replies !   View Related
Character Conversion

I'm using an ETL tool called DecisionStream.

On a particular SQL Server, when it performs an insert, the degree character is being converted to a stacked bar:

source data: 1332 NS 4° Tall 32 oz
after insert: 1332 NS 4¦ Tall 32 oz

This works fine on my laptop with SQL Server, but for some reason, on the SQL Server at work, this behaviour occurs.

Any input would be great.


View Replies !   View Related
BCP Delivery The: ° Character
Using the BCP utility ->

With a degree character in the flat data file, can anyone deliver this to a table without SQL Server changing it to stacked bar: ¦

CREATE TABLE "dbo"."F_conv"



mydata.dat file

1332 NS 4° Tall 32 oz

bcp command

bcp "MY_DB"."dbo"."F_conv" in "mydata.dat"
-q -S<server> -Usa -Psa -f"myformat.fmt"

View Replies !   View Related
BCP Character Conversion
I bcped in data into a database with SQL_Latin1_General_Cp1_CI_AS collation. The input data has an embedded character ®(ascii 174). I did not specify any code page using the -C parm. The data was converted to character «(ascii 171). I ran the bcp trying -C1252 and -CRAW and both maintained the correct character. -C437 and -COEM change the character to «.
Why did this happen? I thought that data would be converted to correctly without any code page specification.

View Replies !   View Related
SQL Get First Character Of A String
Is there a function in SQL that will find the first character of a character string?

Select Org from PR where id = '100'

Normally, Org will retuen a string such as '1:00'. But, all I want from this string is the first character, '1'.

Thank You

View Replies !   View Related
Extra Character

I used excel to import data to my database, I found out a problem, my program is linked with the database, when the program show data from the database, it has an extra '@' symbol, In order to remove it, I need to go to the database to press space bar and backspace at the field. How could I use SQL instead of using space bar and backspace?


View Replies !   View Related
Special Character

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.


View Replies !   View Related
Occurences Of A Character
How can I get a count of occurences of a specific character in a string?

View Replies !   View Related
Character Types
i am using sql server 2000, i am new to databases, could you please tell me what are the different character types, example "varchar , numeric...etc"
How to find out the difference between them....
Where do i find a tutorial for this kind of basic knowledge ?


View Replies !   View Related
Character Length In SQL
What is the syntax to count the # of characters in any given field in Transact SQL?

View Replies !   View Related
Changing Character Set
We are running SQL Server 6.5 with ISO 8859-1 (Latin-1) character set, and plan
to replace it with SQL Server 7.0 with CP1250 character set.

Should we expect any problems? (We plan to migrate data using DTS.)


View Replies !   View Related
Insert A Character
1)How do insert a Qoute after the the last digit on this number '2208ZX12 so it looks like '2208ZX12' in sql 2000.Please show me an easier and faster way cause l have 14000 records.

View Replies !   View Related
Insert A Character
1)How do insert a Qoute after the the last digit on this number '2208ZX12 so it looks like '2208ZX12' in sql 2000.Please show me an easier and faster way cause l have 14000 records.

Ans:- update SomeTable
set SomeField = rtrim(SomeField) + char(39)
where right(rtrim(SomeField),1) <> ''''

Thanks Bill for the solution it works.What l need now is an explanation so l can understand what l just did. Am l right in saying that the reason the char is 39 is to ensure that all fields regardless of length will have the qoute inserted at the end? What if the insert was to be at the beggining how would the query look like?

explain this portion (where right(rtrim(SomeField),1) <> '''') why those two qoutes?Thx a mil

View Replies !   View Related
Insert Character
Initialy l wanted to insert a quote at the beginning and end of the filed. But l went on to try and insert a comma after the quote. Below are the steps that l followed. It basically gave me what l wanted to see but the problem is that ist got spaces in between which are too big and l getting an error as listed below.

Error Message
Server: Msg 8152, Level 16, State 9, Line 3
String or binary data would be truncated.
The statement has been terminated.
'1000101DF000' ,'1000101DF002' ,'1000101DF004' ,'1000101DF006' ,'1000101DF008' ,'1000101DF010' ,'1000101DF012'

What am l doing wrong from step 4? Is it possible to insert quote and a comma then remove the last comma in the table not printing to screen like l'm doing? If so how. Thx for the help thus far

Step 1:-
/** Select a 100 records then insert the characters **/
select top 100 * into Temp1 from Temp

Step 2 :-
/** Insert a quote at the beginning of a record **/
update Temp1
set field = char(39) + rtrim(field)
where left(Field,1) <> ''''

Step 3 :
/** Insert a quote at the end of a record **/
update Temp1
set Field = rtrim(Field) + char(39)
where right(rtrim(Field),1) <> ''''

Step 4 :
/** Inserting a comma after the qoute at the end of the record **/
Declare @Temp1 Varchar(200)
Set @Temp1 = ''
Update Temp1
Set @Temp1 = @Loan2 + Temp1 + ','
Set @Temp1 = Substring(@Temp1,1,len(@Temp1)-1)
-- to remove extra comma at the end
print @Temp1

View Replies !   View Related

Copyright © 2005-08, All rights reserved