PATINDEX And '[' Character

Jul 20, 2005

Hi all

Using SQL Server 2000 SP3 on WIn2K Pro with SP2.

When I do this

SELECT PATINDEX('%[%', 'ABC[DEF')
GO

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



This works as I expect:

SELECT PATINDEX('%]%', 'ABC]DEF')
GO


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

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

PATINDEX

Jul 19, 2000

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.


SELECT
CONVERT(char(16), SUBSTRING(c1,
1,
PATINDEX('% %', c1))) SSI_House_Number,
CONVERT(char(15), SUBSTRING(c1,
PATINDEX('% %', c1)+1,
LEN(c1)-PATINDEX('% %', c1)+1)) SSI_Directional
FROM
t1
WHERE
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.

Thanks,
Daimon

View 1 Replies View Related

PATINDEX

Feb 13, 2007

Hello everyone:

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

SELECT 'a company's name'

thx,

F.

View 5 Replies View Related

Working With Patindex

Nov 7, 2007

hi!
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)
AS
BEGIN
select
Id,
OriginalName,
ContentType,
FullName,
Department,
DateCreated,
Client,
username
from Files_Dyn where PATINDEX('%' + @SearchParam + '%', OriginalName) > 0


END"

View 5 Replies View Related

Question On Using Patindex

Jul 23, 2005

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 11 Replies View Related

Problem With PATINDEX Function

Aug 21, 2007

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?
Thanks,
Gaurang Majithiya

View 3 Replies View Related

How To Search Using PATINDEX Function...Plz Help

Feb 11, 2007

Hi,

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 7 Replies View Related

Question Using Patindex In Sql Server

May 1, 2007

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

PATINDEX Used In A CASE Statement

Sep 6, 2013

I have a number of columns I'm selecting from a table, and I'd like to use PATINDEX to search for a string within a field. Upon finding this string it would return another string...:

SELECT
CASE PARTNERID
WHEN PATINDEX('%INX%',PARTNERID)>0 THEN 'AXXENT'
ELSE PARTNERID
END AS PARTNERID

However, I'm getting this error:Incorrect syntax near '>'.

View 3 Replies View Related

PATINDEX / LIKE - How To Do A Regular Expression In One Of These?

May 8, 2008

Hi

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

REPLACE
(
TheField,
SUBSTRING

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

)
,
'FOUND'
)



QUESTION:

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

Substring With Patindex - Break Value Into 5 Different Columns

Dec 18, 2013

I have a value in a column '01-08-087-0101W5'. I would like to break this value down into 5 different columns. The column will be broken down at the dashes , so the 5 columns will have values like

01
08
087
101
5

I would also like to trim any leading 0's in the above values. i have been trying the following

SUBSTRING(column1,patindex('%[^0]%',column1) ,2) AS FROM_TWP

View 9 Replies View Related

PATINDEX - Search For More Than One Email Domain

Sep 30, 2014

I want to search a table and display only results that contain particular Hotmail and yahoo email addresses. The code I am using currently is:

Select Name, Email, PATINDEX('%Hotmail%, Email) From tblPerson

How do I search for more than one email domain? Do I use a case statement, if so how?

View 2 Replies View Related

Using PatIndex To Find All Numeric Values

Feb 14, 2008

Hello,
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!

CSDunn

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

Validating Inputs And Finding Patterns With PatIndex

Sep 5, 2007

---Checks that input only contains numbers
if PatIndex('%[^0-9]%','11') > 0
Begin
Print 'Not all numbers'
End
Else
Begin
Print 'All numbers'
End

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


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

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

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


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

Namwar
<Link removed by georgev>

View 13 Replies View Related

Substring And Patindex Having An User Defined Function

Oct 30, 2007

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)



Mannu.

View 1 Replies View Related

Need Alternative To PATINDEX In Derived Column Transform

Oct 30, 2007

Hello,
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!

cdun2

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

Problem With PATINDEX Function For Case-sensitive Information

Aug 21, 2007

Hi,

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?

Thanks,

Gaurang Majithiya

View 7 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 2012 :: Patindex Function - Cannot Retrieve Or Extract Single Numeric Value

Jul 17, 2015

I would like solving the following issue using the Patindex function i cannot retrieve or extract the single numeric value as an example in the the values below i would like retrieve the Value 2, but in my result set the value 22 also appears or it is completely omitted.

"2 8 7"
"2 8"
"2"
"22"
"3 2 8"

I have tried the following

Patindex('%[2]% [^0-9] [^1] [^3] [^4] [^5] [^6] [^7] [^8] [^9]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col3,

Patindex('[2]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col4,

Patindex('%[2][^0-9]%',Replace(Marketing_Special_Attributes,'^',' ')),

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

Character Set

Oct 6, 2000

Hi,
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 ?

Bob

View 1 Replies View Related

The ' Character

Nov 16, 2006

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:

select
'<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,
DISP_DATE as Date
from
DISCPLIN D
left outer join
V_DISP V on (V.CODE = D.DISP_CODE)
where
EMPLOY_REF = :EDIT_REF
order by
DISP_DATE DESC


Can anyone suggest how i get round this?

thanks for reading peeps

View 7 Replies View Related

Using BETWEEN With Only One Character

Nov 17, 2006

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

View 1 Replies View Related

Character 0

Feb 18, 2007

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.

Tks

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

View 3 Replies View Related

Tab Character

Jul 23, 2005

what nchar() value equals the tab character. I need to replace it in astring.

View 3 Replies View Related

What Is This Character/why Am I Seeing It?

Feb 29, 2008

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 12 Replies View Related

Get Certain Character

Mar 5, 2008



Hi
I have got a column having data like
RT 12.5R20 (P) OL
RT 12.5R244(SP)GRADER


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

View 5 Replies View Related

Replace, The ' Character, And Yea...

Aug 22, 2007

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 4 Replies View Related

Max Character In Field

Aug 1, 2004

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,

Zath

View 1 Replies View Related







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