I have a set of data where a column contains titles which have been formatted as follows:
"FirstWordSecondWordThirdWord...." etc.
That is, all the words have been concatenated but can be visually separated by their capital first letters.
For reporting purposes, I need to break this column into the separate words so that it looks like:
"First Word Second Word Third Word...." etc.
how this can be achieved?
I am trying to do a select statement with SQL 2000 for all recordscontaining any number of lowercase letters. I have tried the following2 statements and they both seem to match both lowercase and uppercaseletters.SELECT * FROM customers WHERE name LIKE '%[a-z]%'SELECT * FROM customers WHERE name LIKE'%[abcdefghijklmnopqrstuvwxyz]%'Can anybody help?
View 4 Replies View RelatedI have a table ("MyData") with string columns that have nvarchar data that looks like this:
ColA
--------
42/90
78/109
I plan to do a mathematical grouping on the numerator (eg: 0-10,11-20,21-30, etc... ), So I need to grab the numerator and turn it into an int. For reasons I can't get into, I have to do this in pure T-SQL.
So my question: How would I write a select statement that regex pattern matches "^([0-9]+)/" on ColA and returns integers instead of text?
E.g. THis Query:
SELECT [magic t-sql syntax] as Converted_ColA from MyData
should return this set of int values:
42
78
I am doing some analysis on our customer base and their payment profiles. I have generated two profile strings, one for whether the balance of an account has gone up or down and one for the size of the balance in relation to the normal invoice amount for the customer. So (for example) the balance movement string will look like this:
UUUDUUUDUUUD-D00 Where U = Up, D = Down, - = no change and 0 = no change and no balance
I want to analyse these strings in two ways. The first is that I want to find customers with a similar pattern: in the example below the first and last patterns are the same, just one out of sync but should be considered the same
Movement Multiple CountRecords
UUUDUUUDUUUD1230123012301175
------------0000000000001163
UDUUUDUUUDUU3012301230121082
The second type of analysis is to find customers whose pattern has changed: in the examples above the patterns are repeated and therefore 'normal' in the records below the patterns have changed in that the first part does not match the second part.
Movement Multiple CountRecords
UUDUUUDUUUUU-----------07
UDUUUDUUUUUU------------7
good way to approach this without either a cursor or a hidden REBAR. The challenge as I see it is that I have to interrogate every string to find out if there is a repeating pattern and if so where it starts and how long it is (heuristic because some strings will start with a repeating pattern and then the pattern may change or deteriorate) and then compare the string for N groups of repeating characters to see if and when it changes and I can't think of an efficient method to do this in SQL because it is not a set based operation.
I have a table called SrcReg which is having a column name called IsSortSeqNo smallint. I am mapping this column in SSIS and the problem comes when I try to execute against different database which has this table but the column name as ISSortSeqNo. I mean both databases having same name but one with upper case. So SSIS fails executing due to meta validation issue.Is there any way to check whether the column name is in small case or upper case through query?
View 8 Replies View RelatedI have two tables in SQL Server--an expression table and a translate table.
DECLARE @EXPRTABLE TABLE
(
expression varchar(100)
)
INSERT INTO @EXPRTABLE
SELECT 'CALL("-","","TEST")'
[Code] ....
I am trying to (for lack of a better way to explain it), overlay the pattern onto the expression and extract the text where the "#" symbols are.
So,
CALL("-","","TEST") using pattern CALL("#","#","#") would return -,,TEST
SUB(2,67,TEST,4,) using pattern SUB(2,#,TEST,4,) would return 67
Select * FROM TAB1 wher AI_TEXT_VALUE like '%PE%'
The unfortunate thing is i get entries like <Perennial allergic rhinitis>
I am looking specifically for the term PE ( All upper case ) But keep in mind, I would need to use the LIKE operator in my where clause.
( just like in the example above )
How can I change my T-SQL text editor from text sensitive to text insensitive?
View 2 Replies View RelatedCan someone point me to a tutorial on how to search against a SQL Server 2000 using a case insensitive search when SQL Server 2000 is a case sensitive installation?
thanks in advance.
We need to install CI database on CS server, and there are some issueswith stored procedures.Database works and have CI collation (Polish_CI_AS). Server hascoresponding CS collation (Polish_CS_AS). Most queries and proceduresworks but some does not :-(We have table Customer which contains field CustomerID.Query "SELECT CUSTOMERID FROM CUSTOMER" works OK regardless ofcharacter case (we have table Customer not CUSTOMER)Following TSQL generate error message that must declare variable @id(in lowercase)DECLARE @ID INT (here @ID in uppercase)SELECT @id=CustomerID FROM Customer WHERE .... (here @id in lowercase)I know @ID is not equal to @id in CS, but database is CI and tablenames Customer and CUSTOMER both works. This does not work forvariables.I suppose it is tempdb collation problem (CS like a server collationis). I tried a property "Identifier Case Sensitivity" for myconnection, but it is read only and have value 8 (Mixed) by default -this is OK I think.DO I MISS SOMETHING ????
View 4 Replies View RelatedHi everyone. I'm developing a web search engine using asp and SQL Server 2000. I need to return records that matches with a string entered by users. In example, suppose my database to have this structure and it's filled like this:
ID NAME KEYWORDS
--- ------- -----------
AA025 NAME1 attached, atic, common, business, hotels
AA026 NAME2 headache, medicin, aspirins, heat, health
AA027 NAME3 at, services, music, electronics
suppose that user enters 'at'. By now, i'm using this pattern '%<input_text>%'. So in this example, pattern would be '%at%'. As i remarked in the fields above, the three fields matches, and that's not what i'm looking for. I want that the result of that query be just the last field, with 'AA027' ID.
Thanks in advance guys.
Cheers.
I've been trying to find and filter out data that can cause FOR XML to fail with the error:FOR XML could not serialize the data for node ?? because it contains a character (0x0006) which is not allowed in XML.I thought it would be simple, just identify the rows that don't match the XML spec URL...
However, the following doesn't work.
select fieldname
from tablename
where fieldname
like N'%[^'+nchar(0x9)+nchar(0xA)+nchar(0xD)+nchar(0x20)+N'-'+nchar(0xD7FF)+N']%'
Not only does this fail to find the offending rows, when I checked by inverted it by taking out the "^" it still returns no rows.When I reduced 0xD7FF down to 0xFF it returns data, but I cannot add the other range back in without all rows not matching.I've experimented with values and found 0x02E9 was the highest I could go without all values vanishing. Even then, numbers lower than that caused a large variation in the number of rows returned.My Field is NVarchar(100) in SQL_Latin1_General_CP1_CI_AS.
I've decalred this stored procedure with a simple where and like statement. The problem is I don't see any result.
Here is my code
create procedure sp_Select_ProfileNames
@NameSearch varchar(50)
as
select ProfileFirstName +' '+ ProfileLastName as ProfileName
from Profiles
where ProfileLastName like '%@NameSearch%';
When i change this line:
where ProfileLastName like '%@NameSearch%';
to
where ProfileLastName like '%Bil%';
I see names starting with Bil, but when i enter Bil as param, I don't get anything.
What's wrong here ?
I want to check for multiple patterns in a particular column.
For one pattern I can write e.g.
SELECT * FROM <TablName> WHERE ColumnName LIKE '%abcd%'.
My requirment is to select all rows for which column value matches with many patterns.I will fetch the patterns in a subquery
e.g. (SELECT '%'+name+'%' FROM <TableName>)
Any thoughts?
I have a table called MessageBoard. It has a column called Messages.
A user can type text including any html tags through a text area ans when he saves it by clicking a button, the content typed by the user is saved in the MessageBoard Table (in the Messages) column. So once saved, the html tags are kept intact. If I have to find and replace certain html tags, what kind of SQL Query I have to write?
For example I want to find all the <pre> </pre> tags and replace it with <p> </p> tags. How do I do this?
The LIKE keyword in SELECT WHERE can use wildcard
%
Any string of zero or more characters.
_
Any single character.
but how to Pattern Matching for "zero or single character"?
I'm not quite sure that this is possible but, I figured that I would check with you experts out there before trying a new approach. I've done quite a bit of research and have not seen anyone quite figure this out yet.
We have a SQL Server 2005 application that stores and indexes documents to the database as an image data type. I'm able to do full-text queries against the documents without any trouble. I begin to run into problems when trying to pattern match social security numbers and drivers licenses stored in a full-text index. I have a user defined function that I call which runs my regular expression that checks for hits of a ssn or license number in the index. I have no problem getting hits when the data sits in a column.
I do need to mention that I have no trouble when searching for a ssn with a fixed value and where I know the ssn (ex: 123-45-6789). I am actually trying to find the existence of the pattern of ###-##-#### (ex: ^d{3}-d{2}-d{4}$) anywhere in the index.
Any help would be very much appreciated.
Chaps,
apologies for the drip-drip approach......
Is it possible to do pattern matching against a string using FINDSTRING or similar in a derived column expression without recourse to including 3rd party regexp style plugins?
I want to seach for a reference in a string which will have the format ANNNNNAAA ie. an alpha with a certain value followed by 5 digits followed by three alphas with specific values.
eg Z00001YYY or X00022HHH
thanks for your assistance,
regards,
Chris
Yesterday I received a response to my CI/CS Collation problem and therecommendation was to try and restore a CI Collation database to a CSCollation database. After creating a blank CS database a full restore(Force restore over existing database) does change the Collation toCI. I'm unsure as to how I can restore without changing theCollation. Any suggestions?
View 2 Replies View Relatedin sql server 2000 or 2003 how can i tell if a database is case sensitive or not??
View 2 Replies View Relatedhi friends, is it possible to make sqlserver case sensitive?
i mean is there any options to set while installation?
thank you very much
Dear SQL Experts,
Can I change an existing 2000 sql server to case-sensative?
Books on-line tell you how to create one during setup, but requirements around here dictate that I chage my existing setup.
Any help would be greatly appreciated.
Thanks
I have a Users table with UserName and Password columns, in SQL Server 2005.
Currently the SQL Server is not set up to use case-sensitive criteria in the sprocs. How can I write my sproc to be case sensitive when searching on username and password?
I have already install my Microsoft SQL server 2000 as not casesensitive, how to I change it to be case sensitive?
View 2 Replies View RelatedHii
I transferred data from Oracle to sql server 2005. Now what i want is to make data in the tables case-sensitive .(it has to be data inside the tables only and not table and column names).
what i tried is :
alter database test collate Latin1_General_CS_AS
But to my horror it made the tables name case-sensitive .
Plz help me out asap.
Thanx in advance
Supriya
Is Windows Login Name Case Sensitive in SQL Server 2000?
View 1 Replies View RelatedI have just been given a new SQL Server 2000 box to look after in production. I just tried to run a standard t-sql script I use for setting up backup jobs and so on. However, it failed with a long list of errors - quite a surprise at first since I have run the same script on many other servers wihtout a hitch. On close examination, the problem appears to be that the new serer is setup with a server default collation... Latin1_General_BIN (I think a binary based collation makes this a case sensitive server).
This is quite an urgenet one since I have to get this wrapped up today. I don't think I can change the server's default collation without a lot of red-tape. Is there a quick way to run my scripts in a 'case insenstive' context within Query Analyzer? If so, how?
Thanks in advance,
Clive
I am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?
Jacob
Hi,
SELECT UserID, UserName, Password, PublisherID, CurrencyFROM [User]WHERE (Password = 'Anitha') I am using the above mentioned it is working but int the password field i had given it as anitha. Now the querry is retriving the record for anitha, it shouldnot happen. The querry should retrive the record of anitha only for where condition anitha and not for Anitha or ANITHA etc..
Thanks
Vishwanath
I am curious with using replication in sql server 2005 one way from db A (source) replicating to db B(destination) in which db A has a collation of CS and db B has a collation of CI. Will there be any problems with this scenario? Thanks in advance!
View 2 Replies View RelatedOver the weekend I decided to give it the ability to do a case sensitive character swap. Updating the code was pretty straight forward but when I was through, I noticed that I was getting Cardinality Estimate warnings that I wasn't getting before.
Anyway, here is some test data and two versions of the executed SQL (the base code is all dynamic and the two code versions are the result of toggling the @MatchCase parameter).
/* ========================================
CREATE TABLE
======================================== */
CREATE TABLE [dbo].[PersonInfoSmall](
[PersonID] [BIGINT] NOT NULL,
[FirstName] [NVARCHAR](50) NOT NULL,
[MiddleName] [NVARCHAR](50) NULL,
[LastName] [NVARCHAR](50) NOT NULL,
[code]....
One of my varchar columns in a table has multiple key words enclosed in a pattern of special characters.
Eg: William Shakespeare was an English [##poet##], [##playwright##], and [##actor##], widely regarded as the greatest [##writer##] in the English language and the world's pre-eminent [##dramatist##]. He is often called England's national [##poet##] and the "Bard of Avon". His extant works, including some collaborations, consist of about 38 plays, 154 [##sonnets##], two long narrative [##poems##], and a few other [##verses##], of which the authorship of some is uncertain. His plays have been translated into every major living language and are performed more often than those of any other [##playwright##].
I need to write to query to find all distinct key words that are enclosed within [## and ##]. My query should yield the following results from the string in the example above
[##actor##]
[##dramatist##]
[##playwright##] -- 2 occurrances, but I need it only once in my result set
[##poems##]
[##poet##] -- 2 occurrances, but I need it only once in my result set
[##sonnets##]
[##verses##]
[##writer##]
I need to run this on a large table, so I am looking for the best possible way to minimize any performance issues.
Just give you sample code, I have provided below 2 separate snippets, one with table variable and another with temp table.
DECLARE @MyTable TABLE (MyString VARCHAR (8000))
INSERT @MyTable VALUES ('William Shakespeare was an English [##poet##], [##playwright##], and [##actor##], widely regarded as the greatest [##writer##] in the English language and the world''s pre-eminent [##dramatist##]. He is often called England''s national [##poet##] and the "Bard of Avon". His extant works, including some collaborations, consist of about 38 plays, 154 [##sonnets##], two long narrative [##poems##], and a few other [##verses##], of which the authorship of some is uncertain.
[code].....