Proper Case Conversion For Celtic Names

Mar 29, 2008

I've used this udf for a while with great success, but only on fields with more than one word....

http://weblogs.sqlteam.com/jeffs/archive/2007/03/09/60131.aspx

I'd like to know how I can adapt this function so it will convert a scottish/irish surname (McDonald or O'Shea) when there is only the surname in the column

This is what I'd been using for multiple words (Ronald McDonald). But it won't work on just Mcdonald. I'm sure it's just a simple tweak, but it all looks Punjabi to me?

Thanks in advance!!



CREATE FUNCTION [dbo].[f_ProperCase](@Text as varchar(512)) RETURNS varchar(512) as
BEGIN

DECLARE @Reset bit
DECLARE @Ret varchar(512)
DECLARE @i int
DECLARE @c char(1)

SELECT @Reset = 1, @i=1, @Ret = ''

WHILE @i <= LEN(@Text)
SELECT @c= SUBSTRING(@Text,@i,1),
@Ret = @Ret + CASE WHEN @Reset=1 THEN UPPER(@c) ELSE LOWER(@c) END,
@Reset= CASE WHEN
CASE WHEN SUBSTRING(@Text,@i-4,5) like '_[a-z] [DOL]''' THEN 1
WHEN SUBSTRING(@Text,@i-4,5) like '_[a-z] [D][I]' THEN 1
WHEN SUBSTRING(@Text,@i-4,5) like '_[a-z] [M][C]' THEN 1
ELSE 0
END = 1
THEN 1
ELSE CASE WHEN @c like '[a-zA-Z]' or @c in ('''') THEN 0
ELSE 1
END
END,
@i = @i +1
RETURN @Ret

-- Test: SELECT dbo.f_ProperCase('it''s crazy! i couldn''t believe kate mcdonald, leo dicaprio, (terrence) trent d''arby (circa the 80''s), and jada pinkett-smith all showed up to [cHris o''donnell''s] party...donning l''oreal lIpstick! They''re heading to o''neil''s pub later on t''nite. the_underscore_test. the-hyphen-test.' )
END

View 4 Replies


ADVERTISEMENT

Proper Names Function

Jul 12, 2004

Hello: a nice simple question (I hope). Is there a MS SQL equivilant to PROPER (string) which would return "Fred Bloggs" from "FRED BLOGGS" and equally from "FrEd bLoggs" ? I cant find such ....


Gerry

View 3 Replies View Related

Converting A String Expression To Proper Case

Oct 22, 2000

How would I convert an expression like on of these to all upper case first letters with remaining letters lower case? VB has a function for that but sql doesn't seem to. I thought about having a loop go through each character to check for spaces. I've written a couple of similar pieces of code in VB when a while ago, but is there a better way? Thanks :)

Just a couple of typical examples of how the data should appear ~

payment, credit card ==> Payment, Credit Card
butcher & singer ==> Butcher & Singer

View 1 Replies View Related

How To Put Few Hundred Million Strings In Proper Case

Sep 16, 2015

First off, I know this is a presentation issue. Second, no, I can't force a change on my source systems.

Some of the systems that send my BI application data, send that data in all upper case like so "JOHN DOE". We have this horrible SQL function that goes through and makes sure that the first letter in a word is always uppercase and the rest of the letters are lower case. So my results are "John Doe".

As you can imagine this is dreadfully slow when executed a couple of hundred million times, but what are my options?

I have not used Data Quality Services yet, but the chart in BOL says a DQS SSIS cleansing task can do 1 million records in 2 hours on a given set of hardware. That is still pretty horrible.

I suppose I could cobble together a Script task in SSIS, but I am pretty sure clumsy dotNet is not going to be much faster.

CREATE FUNCTION [dbo].[udf_ProperCase](@UnCased varchar(max))
RETURNS varchar(max)
as
begin
declare @Reset bit;
declare @Ret varchar(max);

[Code] .....

View 14 Replies View Related

Transact SQL :: Upper Case To Lower Case Conversion

May 4, 2015

I have column with value of all upper case, for example, FIELD SERVICE, is there anyway, I can convert into Field Service?

View 7 Replies View Related

Case Sensitive Object Names?

Mar 19, 2007

My new DB seems to be "case-sensitive". I have a table named Bld_List.

Select * From Bld_List

returns all records in QA. However, if I don't follow the correct case

Select * From bld_list

I get this error: Invalid object name 'bld_list'.

Also, when I try to get rid of some unnecessary records by using this

DELETE
FROM Bld_List
WHERE (PRODUCT IN
(SELECT DISTINCT Product
FROM BldOff_Inv_Daily))

I get this error: Cannot resolve collation conflict for equal to operation.

I imagine I set something up wrong when I first built the DB, but I don't know what to look for.

Thanks

View 7 Replies View Related

Change Result Names Besides CASE?

Aug 13, 2012

Example:

Select StoreId from tblStores
S1
S2
S3
S4

I want to display a more friendly name. I have been using a case statement:

select case
WHEN storeId = 'S1' THEN 'NY Store'
WHEN storeId = 'S2' THEN 'CA Store'
...
end AS StoreName

If I am doing a simple 1:1 mapping like that, is there a better way than using a case statement (without creating/modifying tables)?

View 2 Replies View Related

Column Names CAsE SeNSitive? - DFT

Aug 23, 2007

Does the column names are case sensitive for the DFT? Recently in one of table, the column names were changed to Upper Case from Lower case and the package started failing in validation. Is there any solution / work around for this?


Thanks

View 7 Replies View Related

Case Conversion

Aug 24, 1999

background: The application is case sensitive and so SQL server has been set up with case-sensitive sort order.

Initially, users were using mixed case in several fields but now they are asking to have everything lower-cased. We can change the application to only allow lower-case entry for these fields from this point forward.

My question is what to do with the previously created records. Would anybody know of a way to run a case conversion routine on all the existing records in the DB?

Thanks in advance

View 1 Replies View Related

Case Conversion With SQL Or Stored Proc

Dec 29, 2006

Hi experts,

I m new in SQL stuff. I have to work out a fucntion with ASP.net for CSV import to the DB in MSSQL.

I would like to know for Stored Proc, is there any way that I can do the case conversion?

e.g

the Full_Name read from CSV file: Lennon, John

Then for the family name I need to convert into uppercase so the converted one: LENNON, John

Is there any way I can check those words before the comma? The CSV file is delimited with | instead of , ?

Can I use substring for that? And also do you have any online tutorial for Stored Procedure recommended? Thanks a lot!!!

Cheers,

KNVB

View 4 Replies View Related

SQL Server 2012 :: Case Statement - Populate Field Names In Table

Apr 9, 2014

Is this a correct syntax to populate a field name PHONES in my CUSTOMERS TABLE

case when(d.phone = (SELECT phone from CALLS where exists(select home_phone, mobile, toll_free from CALLS2) Then 1 END 'PHONES'

View 1 Replies View Related

CASE Throws Conversion Failed When Converting Datetime From Character String Exception

May 1, 2008



Hi All,

I have SQL query as:


SELECT CASE @sortcolumn

WHEN 'TYPE' THEN TYPE_TXT

WHEN 'DATE' THEN CHECKOUT_TS

WHEN 'ACCT' THEN ACCT_NBR

WHEN 'FIRST' THEN FIRST_NM

WHEN 'LAST' THEN LAST_NM

WHEN 'DAYS' THEN DATEDIFF(dd, CHECKOUT_TS, GETDATE())

ELSE DELIVER_ID END

FROM REQUEST

If @sortcolumn has 'DATE' or 'DAYS' it runs fine....any other value, I get an exception "Conversion failed when converting datetime from character string". All columns except CHECKOUT_TS are of type varchar.

Any help would be greatly appreciated.

Thanks,
Shashi

View 3 Replies View Related

Dynamically Pass Table Names And File Names To IS Package

Mar 1, 2015

I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.

The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.

View 1 Replies View Related

Integration Services :: Chinese Names Export To CSV - Garbled Names

Aug 11, 2015

As part if a recent requirement I have to export Chinese/Singaporean names in a CSV file. The data in the tables is a NVARCHAR(256).

I am using a FlatFile Connection manager where all the present columns from the table are exported as NVARCHARs. My understanding was that the Chinese/Singaporean names would blend seamlessly with NVARCHARs in place. But, they get garbled when pushed to the CSV.

Here is the connection manager setup

There are a lot of suggestions of fixing this by copying/pasting to a notepad file and changing the formatting... But I cant do that since the file is generated using a schedules SSIS package. How can I tweak the process to fix the issue?

View 4 Replies View Related

Determine Table Names And Column Names At Runtime?

Jan 22, 2004

Hi

I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.

Thanks.

View 5 Replies View Related

DB Mirroring: Different Server Names With Same Instance Names

Jan 31, 2008

I'm going to be setting up DB mirroring between two SQL Server 2005 boxes. Since these are on two different servers, can the instance names be the same? Is there any reason NOT to do so if the mirror server is going to be used exclusively for DB mirroring?

For example: if the my primary DB is located on SERVER1INSTANCE1, can the mirror be SERVER2INSTANCE1 or do the instance names have to be different even though they're on different boxes.

Thanks!

View 4 Replies View Related

SQL And Proper Concatenation Within VB

Jun 9, 2008

Hi all - I have posted inquiries on this rather vexing issue before, so I apologize in advance for revisting this. I am trying to create the code to add the parameters for two CheckBoxLists together. One CheckBoxList allows users to choose a group of Customers by Area Code, the other "CBL" allows users to select Customers by a type of Category that these Customers are grouped into. When a user selects Customers via one or the other CBL, I have no problems. If, however, the user wants to get all the Customers from one or more Area Codes who ALSO may or may not be members of one or more Categories; I have had trouble trying to create the proper SQL. What I have so far:Protected Sub btn_CustomerSearchCombined_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_CustomerSearchCombined.Click        Dim CSC_SqlString As String = "SELECT Customers.CustomerID, Customers.CustomerName, Customers.CategoryID, Customers.EstHours, Customers.Locality, Category.Category FROM Customers INNER JOIN Category ON Customers.CategoryID = Category.CategoryID WHERE "        Dim ACItem As ListItem        Dim CATItem As ListItem        For Each ACItem In cbl_CustomersearchAREA.Items            If ACItem.Selected Then                CSC_SqlString &= "Customers.AreaCodeID = '" & ACItem.Value & "' OR "            End If        Next        CSC_SqlString &= "' AND " <-- this is the heart of my problem, I believe        For Each CATItem In cbl_CustomersearchCAT.Items            If CATItem.Selected Then                CSC_SqlString &= "Customers.CategoryID = '" & CATItem.Value & "' OR "            End If        Next        CSC_SqlString = Left(CSC_SqlString, Len(CSC_SqlString) - 4)        CSC_SqlString &= "ORDER By Categories.Category"        sql_CustomersearchGrid.SelectCommand = CSC_SqlString    End SubAny help on this is much appreciated, many thanks -- 

View 5 Replies View Related

T-SQL Proper Function

May 23, 2005

T-SQL offers UPPER and LOWER functions for formatting text strings. Is there a PROPER function? Thanks.

View 1 Replies View Related

What Are The Proper Procedures?

Sep 18, 2000

What are the proper procedures to move a SQL 6.5 to another 6.5 (new box)

I need to move everything including stored procedures?

Any tips would be helpful.

Thanks,
Jason

View 1 Replies View Related

Proper Way To Us And With If Clause

Apr 3, 2006

I have a tsql where I need to do a patindex on a variable and check if a record exists to meet the where clause for a IF statement below. What am I doing wrong

declare @l_orderid int
set @l_orderid = 18
declare @l_SIGShort varchar(20)
set @l_SIGShort = '~KOP~'
-- KOP Orders
print patindex ( '%~KOP~%', @l_SIGShort)
if patindex ( '%~KOP~%', @l_SIGShort) <> 0 and (if exists (select * from orderoptions
where orderid = @l_orderid and ordertype = 'KOP'))

View 2 Replies View Related

Table Names And Field Names

Jan 21, 2004

I'm trying to do an update query that looks like this:

UPDATE

PAEMPLOYEE

SET PAEMPLOYEE.LOCAT_CODE = EMPLOYEE.PROCESS_LEVEL


FROM

PAEMPLOYEE A

JOIN EMPLOYEE B ON A.EMPLOYEE = B.EMPLOYEE

It's erroring out on the Employee prefix B.EMPLOYEE saying:

..."does not match with a table name or alias name used in the query"


Is it wrong or will it cause problems to have a field name the same as the table name?

View 5 Replies View Related

Server Names Or Instance Names

Aug 3, 2006

Hello there. I'm trying to populate a drop down box with a list of all available instances of SQL server (express or not) available on a network. To list all instances I'm using EnumAvailableServers() in SmoApplication.

The issue is that the Instance column in the returned data table is always empty, and both the Name and the Server columns contain the same string -- the name of the computer the SQL server is installed. Locally I have SSE installed, the instance name is <computer_name>SQLEXPRESS, however, using Smo I can't seem to get to the either full instance name or just the instance name. I can't assume SQLEXPRESS, since there may be more than one installed.

Alternately I used EnumRegisteredServers() in SmoApplication.SqlServerRegistrations and that, while returning instance information (can be retrieved from the RegisteredServer in the returned collection), doesn't always return the remote SQL servers in the list.

Any idea how can this be done? Maybe I'm going at it the wrong way, maybe there's another class / method I need to use to retrieve all instances?

Thank you

View 6 Replies View Related

Going From SQL 7.0 Beta To SQL 7.0 Proper - Problem ...

Jan 14, 1999

Greetings, I seem to be getting a problem during installing SQL 7.0 over the SQL 7.0 beta. It tells me that there are ODBC components need to be upgraded and they are read only ... I can find no way of changing this.

Alternatively if I remove the beta and then install the proper version, will all the old db created under the beta still be recognised ?

Kris Klasen

Act. Manager, Data Warehouse Project
Information Management Branch
Department of Education

E-mail: Kris.Klasen@Central.Tased.Edu.Au
http://www.tased.edu.au
Tel: 03 6233 6994
Fax: 03 6233 6969
Mobile: 0419 549237
73 Murray Street
2nd Floor
Hobart 7000
Tasmania
Australia

View 1 Replies View Related

Proper SQL Backup Procedure

Mar 18, 1999

Greets!

I have been told that simply stopping the SQL server service and backing up the data directory is all I have to do to do a backup of my data. Is this accurate?

Thanks,
Jimmy Ipock

View 2 Replies View Related

Proper Place For Sub Select?

Feb 14, 2013

I have a very simple query that gets a field to use as constraints in another query.

Code:

SELECT A.RIN
FROM apcType T
INNER JOIN apcAttribute a on A.T = T.RIN
WHERE T.Name like '%Sales'

The results of the first query are used in the following query where it is bolded and marked with and <<<<========

Code:
SELECT AP.Arg2, AP.Arg3, M.Parcel,
M.Serial, M.Name, M.Acres, M.District,
V.YearBuilt, V.Code, V.Size,
(SELECT SUM(V1.Acres)
FROM TRValue V1
WHERE V1.Year = V.Year and V1.Parcel = V.Parcel and
SUBSTRING(V1.Code, 1, 1) = 'L'

[code]....

My question is How can I fold the first query into the second?

View 1 Replies View Related

Proper Way Of Linking Tables?

May 2, 2015

I am no stranger to Databases, I worked a lot with MySQL but never really cared about proper DB design as long as it worked. Now I am playing with SQL in a ASP.NET project and want to get things done the right way.Let's say I have a Movies database. My movies can have multiple genres so I set my tables up like this:

[Movies]
MovieID
MovieName
MovieRelease

[code]....

Is this the proper way of doing things? The problem with this is when I want to enter a record manually I have to know the ID of the movie and the ID of the Genres of the movie. And what about naming conventions? By default the identifier is always Id, from my MySQL experience I liked naming it like the table, same goes with other columns. This is my T-SQL code for above tables in VS-2013.

CREATE TABLE [dbo].[Movies] (
[MovieID] INT IDENTITY (1, 1) NOT NULL,
[MovieName] VARCHAR (50) NOT NULL,
[MovieRelease] NUMERIC (18) NOT NULL,
CONSTRAINT [PK_Movies] PRIMARY KEY CLUSTERED ([MovieID] ASC)

[code]....

View 2 Replies View Related

Convert To Proper Date

Apr 16, 2007

I read some questions where questioners ask "Sometimes client gives data where dates are expressed as float or integer values.
How do I find maximum date?".

Ex
March 02, 2006 can be expressed as
02032006.0
020306
2032006
20306
020306.0000
2032006
Assuming the values are expressed in dmy format

The possible way is convert that value into proper date so that all types of date related calculations can be done
Create function proper_date (@date_val varchar(25))
returns datetime
as
Begin
Select @date_val=
case when @date_val like '%.0%' then substring(@date_val,1,charindex('.',@date_val)-1)
else @date_val
end
return
cast(
case
when @date_val like '%[a-zA-Z-/]%' then case when ISDATE(@date_val)=1 then @date_val else NULL end
when len(@date_val)=8 then right(@date_val,4)+'-'+substring(@date_val,3,2)+'-'+left(@date_val,2)
when len(@date_val)=7 then right(@date_val,4)+'-'+substring(@date_val,2,2)+'-0'+left(@date_val,1)
when len(@date_val)=6 then
case when right(@date_val,2)<50 then '20'
else '19'
end
+right(@date_val,2)+'-'+substring(@date_val,3,2)+'-'+left(@date_val,2)
when len(@date_val)=5 then
case when right(@date_val,2)<50 then '20'
else '19'
end
+right(@date_val,2)+'-'+substring(@date_val,2,2)+'-0'+left(@date_val,1)
else
case when ISDATE(@date_val)=1 then @date_val else NULL end
end
as datetime
)
End

This function will convert them into proper date
select
dbo.proper_date('02032006.0') as proper_date,
dbo.proper_date('020306.000') as proper_date,
dbo.proper_date('02032006') as proper_date,
dbo.proper_date('020306') as proper_date,
dbo.proper_date('20306') as proper_date,
dbo.proper_date('020306') as proper_date

Apart from converting integer or float values to date, it will also convert date strings to date
Select
dbo.proper_date('March 2, 2006') as proper_date,
dbo.proper_date('2 Mar, 2006') as proper_date,
dbo.proper_date('2006 Mar 2') as proper_date,
dbo.proper_date('2-Mar-2006') as proper_date,
dbo.proper_date('3/02/2006') as proper_date,
dbo.proper_date('02-03-2006') as proper_date,
dbo.proper_date('2006/03/02') as proper_date,
dbo.proper_date('March 2006') as proper_date,
dbo.proper_date('2 Mar 2006') as proper_date


Madhivanan

Failing to plan is Planning to fail

View 8 Replies View Related

Proper Way To Return SCOPE_IDENTITY From SP

Mar 31, 2008

What is the proper way to return the identity of a newly inserted row from a stored procedure? Using a return value or a select statement? (I guess as an output parameter should also be considered...) As in

RETURN SCOPE_IDENTITY()

or

SELECT SCOPE_IDENTITY()

What are the pros/cons of using one approach over the other?

- Jason

View 4 Replies View Related

Transact SQL :: XML Not Using Proper Index?

Aug 26, 2015

I have two xml queries that take long: the 1st query takes about 5 minutes (returns 700 rows) and the 2nd query takes about 10 minutes (returns 4 rows). The total rows in the table is about 2 million. There are three secondary indexes: Property, Value and Path in addition to the clustered index on CardID and Primary XML index. Here is the table definition: 

CREATE TABLE [dbo].[Cards]
(
[CardId] [int] NOT NULL,
[Card] [xml] NOT NULL,
CONSTRAINT [PK_dbo_Cards_CardId] PRIMARY KEY CLUSTERED
([CardId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

[code]...

Looking at the execution plan, the query uses the Primary XML Index even if I add any of the secondary xml indexes. My question is why does not the optimizer use the Property 2ndary index instead of the Primary XML Index? Microsoft recommends that creating a Property index for Value() method of the xml datatype would work to provide a performance benefit. What would be another alternative to make the query run faster?

View 12 Replies View Related

Proper Format Of BufferTempStoragePath

Dec 11, 2006

Hello, I have one package that seems to have continuous problems with memory. As of right now it loads a little over 1 million records. I tried leveraging the property, BufferTempStoragePath, but I don't seem to have the right path name. What sort of path do you put there? File? Folder? If it is a file, what sort of file should it be... text, dat, xml? If someone could point me in the right direction it would be greatly appreciated. Thanks.

PS: Below are the error messages I am getting:
[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 4 buffers were considered and 4 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.

[DTS.Pipeline] Error: The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission.

View 3 Replies View Related

Proper Use Of Event Notifications

Apr 19, 2006

Hi.

I'm developing an app that uses Service Broker queues to allow a customer to create "events" that fire using a timer or a query notification. When these events fire, a message is sent to a Service Broker queue for processing. Because there is much managed code involved in processing these messages, I decided to use the External Activator application and an Event Notification to process these messages. My question is "what is the difference between using the External Activator application to launch another application (which simply RECEIVEs a message from the target queue and processes it) and creating a windows service that simply monitors the target queue (with a WAITFOR = -1 clause) and processes it?"

I guess I'm not sure how using the QUEUE_ACTIVATION Event Notification is really helping me.

Thanks,

Chris

View 4 Replies View Related

Getting Date Into Proper Format

May 22, 2007

Hi,



I have a .csv file that lists date like this: "20070522", no hyphens or spaces. In the file connection manager I have the column defined as string. The database column is a datetime.



When I attempt to load the file to the table, I get this error:



[OLE DB Destination [9]] Error: There was an error with input column "effective date" (155) on input "OLE DB Destination Input" (22). The column status returned was: "The value could not be converted because of a potential loss of data.".



My question is, what do I need to do to this column so that I can load it into the database?



Thanks much

View 6 Replies View Related

Proper Way To Drop Publication?

Dec 13, 2005

Hi There

View 4 Replies View Related







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