Regular Expressions In SQL Server 2008

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Regular Expressions In SQL Server 2008

Author

Topic

TittiSkaria
Starting Member

16 Posts

Posted - 2010-09-28 : 09:48:27

Hi Friends ,
I need ur help to solve my problem.I have to use RegularExpressions or Contains Check or Something like LIKE clause to get my values.I need to check my string should only contain an equalto(=) or comma(,) before a specified string.

ie)FirstName=Titti,LastName=Skaria. Which is value of my column name "Name"

I will give LastName to a paricular query to check whether the string before this had an '=' or a ',' infront of it.

Can any one help me???

Thanks And Regards
TittiSkaria.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-09-28 : 10:16:49

Try:

--Assuming that LastName will always be there in the value of the column. In other case you need use case statement and check whether the text "LastName" is present in value and thereafter use the substring method.

select Substring( Name, PatIndex('%LastName%',Name) -1, 1) from yourtable

select Substring( Name, CHARINDEX('LastName',Name) -1, 1) from yourtable

--I have not carried out any testing.

Regards,
Pramod

I am here to learn from Masters and help new bees in learning.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-28 : 10:34:59

quote:

I need to check my string should only contain an equalto(=) or comma(,) before a specified string.

Easy like condition


DECLARE @searchString VARCHAR(255)
SET @searchString = 'foo'

SELECT
    t.*
FROM
    <table> AS t
WHERE
    t.<column> LIKE '%[=,]' + @searchString + '%'

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

TittiSkaria
Starting Member

16 Posts

Posted - 2010-09-29 : 01:22:18

Hi thanks for your replies.Yes i will have LastName in all my Columns 'Name'.I have to select only those datas from my column 'Name' which has 'LastName' at the end and it should only contain an equalto(=) and comma(,) before 'LastName' string.Then i cant solve it using above two methods,Sorry as im new to sql.Can some one pls post me very simple code which i can understand easily..please.

Thanks And Regards
TittiSkaria.

TittiSkaria
Starting Member

16 Posts

Posted - 2010-09-29 : 03:57:09

Hi any one there to help me ?

Thanks And Regards
TittiSkaria.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-29 : 04:11:00

did you try my suggestion?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-29 : 04:20:46

Can you please show us the table structure and some sample data as well. If you do you'll get a good answer almost immediately.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

TittiSkaria
Starting Member

16 Posts

Posted - 2010-09-29 : 05:29:53

Hi Charlie,
Of course i have tried,but it shows syntax error at searchstring.So what to do?
My table name is 'Personal Details' and column name is 'Name'.Can you offer me query for this.i have tried SELECT t.* FROM <PersonalDetails> AS t WHERE t.<Name> LIKE '%[=,]'" + @textbox1.text + " '%'";.Is this correct?.My need is i only want to get those values which should contain only one = and , before the entered string in the textbox.ie}if i enter LastName=Skaria it should return FirstName=Titti,LastName=Skaria and not Initials =M,FirstName=Titti,LastName=Skaria from another row.I need this value only when i enter FirstName=Titti,LastName=Skaria.Now the result will have searchstring along with = and , infront of it.Sorry if im confusing u and wasting your time.

Thanks And Regards
TittiSkaria.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-29 : 05:37:38

Ok -- where I say <table> and <column> I'm expecting you to put your real table and column name in. Sorry thought that was pretty self explanatory. (Obviously without the < and >)

Try this


-- This is a local variable. It holds the string you want to search for
DECLARE @searchString VARCHAR(255)

-- This is where you set the value of the variable
SET @searchString = 'Skaria'

-- This is the select statement
SELECT
    pd.*
FROM
    personalDetails AS pd
WHERE
    pd.Name LIKE '%[=,]' + @searchString + '%'

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-29 : 05:41:24

quote:
Originally posted by TittiSkaria

Hi Charlie,
Of course i have tried,but it shows syntax error at searchstring.So what to do?
My table name is 'Personal Details' and column name is 'Name'.Can you offer me query for this.i have tried SELECT t.* FROM <PersonalDetails> AS t WHERE t.<Name> LIKE '%[=,]'" + @textbox1.text + " '%'";.Is this correct?.My need is i only want to get those values which should contain only one = and , before the entered string in the textbox.ie}if i enter LastName=Skaria it should return FirstName=Titti,LastName=Skaria and not Initials =M,FirstName=Titti,LastName=Skaria from another row.I need this value only when i enter FirstName=Titti,LastName=Skaria.Now the result will have searchstring along with = and , infront of it.Sorry if im confusing u and wasting your time.

Thanks And Regards
TittiSkaria.

Not to worry -- you are new here and don't know how to ask questions yet.

Please read what you have written and try and appreciate how difficult it would be for someone that doesn't know the tables and data involved to help you.

This is how you can help us to help you:

1) Post FULL table structure data for the table(s) involved. You can do this by right clicking on the table in management studio and going to "Script Table AS...." Then copy the resultant sql into the forum.

2) Provide sample data for the table in the form of an INSERT statement.

3) Provide the EXACT required result from this sample data in (2).

With that we'll be able to see exactly what you want and there is no possibility of misreading the situation.

For example I still don't know what your data is like -- it sounds like you have multiple lines that would satisfy the LIKE condition and only want to bring back one of them but I just can't tell.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

TittiSkaria
Starting Member

16 Posts

Posted - 2010-09-29 : 05:48:33

Hi,This is how my table looks like.
Sl.No Name
1 Initials=M,FirstName=Titti,LastName=Skaria
2 Initials=M,FirstName=John,LastName=Skaria
3 FirstName=Titti,LastName=Skaria
4 FirstName=John,LastName=Skaria
5 Initials=M,FirstName=Titti,SurName=Thomas,LastName=Skaria
6 Initials=S,FirstName=John,SurName=Thomas,LastName=Skaria

All My values used to ends with LastName=Skaria.If i enter LastName=Skaria it should return only FirstName=Titti,LastName=Skaria and not other values.Also if enter FirstName=Titti,LastName=Skaria it should return only first row bcoz only this row ends with search string and has only one required symbol each before it.

Thanks And Regards
TittiSkaria.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-29 : 06:01:31

So are you actually going to pass a searchstring like 'LastName=Skaria'

This is *exactly* what you are actuall passing to the database? A string.

And you always want the first row returned?

Then


DECLARE @searchString VARCHAR(512)

SET @searchString = 'LastName=Skaria'

SELECT TOP 1
    pd.*
FROM
    personalDetails  AS pd
WHERE
    pd.Name LIKE '%,' + @searchString + '%'
ORDER BY
    pd.[Sl.No]

Your table design is bad. Why are you storing a person's details like this? It would be much better to have a column for firstname, a column for surname, a column for initials, etc.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

TittiSkaria
Starting Member

16 Posts

Posted - 2010-09-29 : 06:34:21

Hi Friend ,
i know this is a bad table design.Thanks for your replies.But my need is like that.Yes im entering LastName=Skaria as search string and not every time.it depends.

i will give any of the formats as follows.

FirstName=Titti,SurName=Thomas,LastName=Skaria should return the 5th row.

FirstName=John,SurName=Thomas,LastName=Skaria should return last row and

LastName=Skaria should return 3rd and 4th row.

only these rows satisfies my need .

Thanks And Regards
TittiSkaria.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-29 : 06:53:26

I think this would do it.


DECLARE @searchString VARCHAR(512)

SET @searchString = 'LastName=Skaria'

SELECT
    pd.*
FROM
    personalDetails  AS pd
WHERE
    pd.Name LIKE '%,' + @searchString
    AND pd.[name] NOT LIKE '%,%,' + @searchString

ORDER BY
pd.[Sl.No]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

TittiSkaria
Starting Member

16 Posts

Posted - 2010-09-29 : 07:34:05

Hi Sorry i cant solve it.This is a sample table which i created for Checking.I have been working in Active Directory Domain(Sorry if u r not working in this Domain or Unaware of it).In this i need to get a result like above to get all Childs based on DistingishedName.
Thanks And Regards
TittiSkaria.

TittiSkaria
Starting Member

16 Posts

Posted - 2010-09-29 : 07:40:47

Hi,

How about using Wildcards along with LIKE Clause to create a query to get the desired result.

Thanks And Regards
TittiSkaria.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-29 : 08:38:48

??????????

The query *does* use LIKE with wildcards

What doesn't work? I'm using SQL SERVER 2008. I'm running this query natively in Microsoft Server Management Studio. Here is the example data you gave



DECLARE @testTable TABLE (
	[Sl.no] INT
	, [Name] VARCHAR(MAX)
	)

INSERT @testTable ([Sl.no], [Name])
      SELECT 1, 'Initials=M,FirstName=Titti,LastName=Skaria'
UNION SELECT 2, 'Initials=M,FirstName=John,LastName=Skaria'
UNION SELECT 3, 'FirstName=Titti,LastName=Skaria'
UNION SELECT 4, 'FirstName=John,LastName=Skaria'
UNION SELECT 5, 'Initials=M,FirstName=Titti,SurName=Thomas,LastName=Skaria'
UNION SELECT 6, 'Initials=S,FirstName=John,SurName=Thomas,LastName=Skaria'

DECLARE @searchString VARCHAR(512)

SET @searchString = 'LastName=Skaria'

SELECT
    pd.*
FROM
    @testTable  AS pd
WHERE
    pd.Name LIKE '%,' + @searchString
    AND pd.[name] NOT LIKE '%,%,' + @searchString
ORDER BY
	pd.[Sl.No]

And guess what -- the script gives the correct values


Sl.no	Name
3	FirstName=Titti,LastName=Skaria
4	FirstName=John,LastName=Skaria

So I don't know what you really want because I've given you what you asked for

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

TittiSkaria
Starting Member

16 Posts

Posted - 2010-09-30 : 00:30:34

Hi Charlie,
Thanks For Your Kind Replies And Also For Spending Time For Me.Atlast I Have Solved it.I Dont Know What Went Wrong.I Have Tried The Above Query And Got My Desired Result.You Are Really Inspirational.Thanks A Lot.

Thanks And Regards
TittiSkaria.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-30 : 04:30:38

Glad it's working for you.

My commend on the database design still stands though. If you can redesign this at some point it will make it a lot easier for you. Also the query performance for the SQL I posted will be poor. I don't think there will be a good way to use any index on your column.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources