Hàm Sql để lấy số từ chuỗi – sql trích xuất số từ giữa chuỗi

Hai nhiệm vụ phổ biến thường được hỏi trong một cuộc phỏng vấn SQL. Viết một truy vấn sql để chỉ trích xuất các bảng chữ cái từ một chuỗi chữ và số nhất định. Viết truy vấn sql để chỉ trích xuất các số từ một chuỗi chữ và số nhất định.

Bạn đang xem : trích xuất số sql từ giữa chuỗi

hàm sql để lấy số từ chuỗi

Trong video này, chúng ta sẽ thảo luận về hai nhiệm vụ phổ biến thường được hỏi trong một cuộc phỏng vấn SQL.

  1. Viết một truy vấn sql để chỉ trích xuất các bảng chữ cái từ một chuỗi chữ và số nhất định.
  2. Viết truy vấn sql để chỉ trích xuất các số từ một chuỗi chữ và số nhất định.

Bảng này chỉ có một cột và nó chứa cả số và bảng chữ cái

chuỗi chữ và số trong cơ sở dữ liệu

Bây giờ, nhiệm vụ trước mắt là viết một truy vấn trích xuất các số và chữ cái thành các cột riêng biệt như bạn có thể thấy bên dưới.

truy vấn sql để trích xuất các số từ một chuỗi

Nếu bạn muốn làm theo, sau đây là tập lệnh SQL để tạo bảng và điền vào bảng đó với dữ liệu thử nghiệm.

  Tạo bảng TestTable
(
IDName nvarchar (25)
)
Đi

Chèn vào các giá trị TestTable ('Nir10ma0la1')
Chèn vào các giá trị TestTable ('1A0ru0na2')
Chèn vào các giá trị TestTable ('S1h00ashi3')
Chèn vào các giá trị TestTable ('N100aga4raj')
Chèn vào các giá trị TestTable ('Sruj100a5n')
Chèn vào các giá trị TestTable ('Sr1u0s0h6ti')
Chèn vào các giá trị TestTable ('Ha1n0u0man7th')
Chèn vào các giá trị TestTable ('Sh10iva08mma')
Chèn vào các giá trị TestTable ('10Sonu09')
Chèn vào các giá trị TestTable ('Nim10m1u0')
Bắt đầu  

Với câu hỏi phỏng vấn SQL này, về cơ bản người phỏng vấn đang kiểm tra xem bạn có biết các khái niệm sau đây không

  • Các chức năng do người dùng xác định và
  • Cách sử dụng các hàm tích hợp sẵn của SQL Server – PatIndex () và Stuff ()

ví dụ về patindex máy chủ sql

Chúng tôi đã thảo luận chi tiết về các Hàm do người dùng xác định trong khóa học Hướng dẫn SQL Server cho người mới bắt đầu . Vui lòng xem video từ Phần 30 đến 33. Chúng tôi cũng đã thảo luận về các hàm PatIndex () và Stuff () trong Phần 24. Sau đây là liên kết đến trang Khóa học SQL Server .

Hướng dẫn SQL Server cho người mới bắt đầu

Patindex trong SQL

  • PATINDEX (‘% Mẫu%’, Biểu thức)
  • Trả về vị trí bắt đầu của lần xuất hiện đầu tiên của một mẫu trong một biểu thức được chỉ định. Nó cần hai đối số, mẫu được tìm kiếm và biểu thức.
  • Nếu không tìm thấy mẫu được chỉ định, PATINDEX () trả về ZERO.

Nội dung trong máy chủ sql

  • STUFF (Biểu_thức gốc, Bắt đầu, Độ dài, Biểu_thức thay thế)
  • Hàm STUFF () chèn biểu thức Replacement_expression, tại vị trí bắt đầu được chỉ định, cùng với việc xóa các đặc tính được chỉ định bằng tham số Độ dài.

Hàm SQL để trích xuất các số từ một chuỗi chữ và số

  Tạo hàm UDF_ExtractNumbers
(
  @input varchar (255)
)
Trả về varchar (255)
Như
Bắt đầu
  Khai báo @alphabetIndex int = Patindex ('% [^ 0-9]%', @input)
  Bắt đầu
    Trong khi @alphabetIndex & gt; 0
    Bắt đầu
      Đặt @input = Stuff (@input, @alphabetIndex, 1, '')
      Đặt @alphabetIndex = Patindex ('% [^ 0-9]%', @input)
    Chấm dứt
  Chấm dứt
  Trả về @input
Kết thúc  

Chức năng tương tự với giải thích mã

 Tạo hàm UDF_ExtractNumbers
(
  - Đầu vào là chuỗi chữ và số
  @input varchar (255)
)
- Trả về số dưới dạng một chuỗi
Trả về varchar (255)
Như
Bắt đầu
  - Trả về chỉ số của một ký tự không phải là số
  - Nếu không tìm thấy mẫu được chỉ định, trả về KHÔNG
  Khai báo @alphabetIndex int = Patindex ('% [^ 0-9]%', @input)
  Bắt đầu
    Trong khi @alphabetIndex & gt; 0
    Bắt đầu
      - Trong chuỗi đầu vào (@input) tại vị trí (@alphabetIndex)
- nơi chúng tôi có một ký tự không phải là số, hãy thay thế ký tự đó 1
- ký tự với một chuỗi rỗng ('')
Đặt @input = Stuff (@input, @alphabetIndex, 1, '')
- Tìm ký tự không phải số tiếp theo và lặp lại bước trên
- cho đến khi tất cả các ký tự không phải số được thay thế bằng một chuỗi trống
      Đặt @alphabetIndex = Patindex ('% [^ 0-9]%', @input)
    Chấm dứt
  Chấm dứt
  Trả về @input
Kết thúc  

Sử dụng hàm do người dùng xác định trong truy vấn

  Chọn dbo.UDF_ExtractNumbers (IDName) làm ID làm Số từ TestTable  

Kết quả

hàm sql để trích xuất số từ chuỗi

Hàm SQL để trích xuất các bảng chữ cái từ chuỗi chữ và số

  Tạo hàm UDF_ExtractAlphabets
(
  @input varchar (255)
)
Trả về varchar (255)
Như
Bắt đầu
  Khai báo @alphabetIndex int = Patindex ('% [^ a-zA-Z]%', @input)
  Bắt đầu
    Trong khi @alphabetIndex & gt; 0
    Bắt đầu
      Đặt @input = Stuff (@input, @alphabetIndex, 1, '')
      Đặt @alphabetIndex = Patindex ('% [^ a-zA-Z]%', @input)
    Chấm dứt
  Chấm dứt
  Trả về @input
Kết thúc  

Chức năng tương tự với giải thích mã

  Tạo hàm UDF_ExtractAlphabets
(
  - Đầu vào là chuỗi chữ và số
  @input varchar (255)
)
- Trả về số dưới dạng một chuỗi
Trả về varchar (255)
Như
Bắt đầu
  - Trả về chỉ số của một ký tự không phải là bảng chữ cái
  - Nếu bảng chữ cái không được tìm thấy, KHÔNG được trả về
  Khai báo @numberIndex int = Patindex ('% [^ a-zA-Z]%', @input)
  Bắt đầu
    Trong khi @numberIndex & gt; 0
    Bắt đầu
      - Trong chuỗi đầu vào (@input) tại vị trí (@numberIndex)
- nơi chúng ta có một ký tự chữ cái, hãy thay thế 1 ký tự chữ cái đó
- ký tự với một chuỗi rỗng ('')
Đặt @input = Stuff (@input, @numberIndex, 1, '')
- Tìm ký tự chữ cái tiếp theo và lặp lại bước trên
- cho đến khi tất cả các ký tự chữ cái được thay thế bằng một chuỗi trống
      Đặt @numberIndex = Patindex ('% [^ a-zA-Z]%', @input)
    Chấm dứt
  Chấm dứt
  Trả về @input
Kết thúc  

Sử dụng hàm do người dùng xác định trong truy vấn

  Chọn dbo.UDF_ExtractAlphabets (IDName) làm Tên từ TestTable  

Kết quả

cách trích xuất bảng chữ cái từ chuỗi chữ và số trong sql

Sử dụng cả hàm do người dùng xác định trong truy vấn SQL

  Chọn dbo.UDF_ExtractNumbers (IDName) làm ID, dbo.UDF_ExtractAlphabets (IDName) làm Tên từ TestTable  

Kết quả

truy vấn sql để trích xuất số từ chuỗi

Xem Thêm  Cách điền trước dòng chủ đề và nội dung trong các email do Mailto tạo - chủ đề và nội dung mailto


Xem thêm những thông tin liên quan đến chủ đề sql trích xuất số từ giữa chuỗi

exec vs sp executesql in sql server

alt

  • Tác giả: kudvenkat
  • Ngày đăng: 2017-04-18
  • Đánh giá: 4 ⭐ ( 7061 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Text version of the video
    http://csharp-video-tutorials.blogspot.com/2017/04/exec-vs-spexecutesql-in-sql-server.html

    Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
    https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1

    Slides
    http://csharp-video-tutorials.blogspot.com/2017/04/exec-vs-spexecutesql-in-sql-server_18.html

    Dot Net & SQL Server Tutorials
    https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

    In this video we will discuss the difference between exec and sp_executesql.

    In SQL Servere we have 2 options to execute dynamic sql
    1. Exec/Execute
    2. sp_executesql

    We discussed sp_executesql in detail in Part 138 of SQL Server tutorial. Please check out that video if you are new to sp_executesql.

    If you do a quick search on the internet for the difference between exec and sp_executesql, you will see that many articles on the web states using exec over sp_executesql will have the following 2 problems
    1. It open doors for sql injection attacks
    2. Cached query plans may not be reused and leads to poor performance

    This is generally true, but if you use QUOTENAME() function you can avoid sql injection attacks and with sql server auto-parameterisation capability the cached query plans can be reused so performance is also not an issue. Let’s understand these with examples.

    What is exec() in SQL Server
    Exec() or Execute() function is used to execute dynamic sql and has only one parameter i.e the dynamic sql statement you want to execute.

    As you can see in the example below, we are concatenating strings to build dynamic sql statements which open doors for sql injection.

    Declare @FN nvarchar(50)
    Set @FN = ‘John’
    Declare @sql nvarchar(max)
    Set @sql = ‘Select * from Employees where FirstName = ”’ + @FN + ””
    Exec(@sql)

    If we set @FN parameter to something like below, it drops SalesDB database

    Declare @FN nvarchar(50)
    Set @FN = ”’ Drop Database SalesDB –”’
    Declare @sql nvarchar(max)
    Set @sql = ‘Select * from Employees where FirstName = ”’ + @FN + ””
    Exec(@sql)

    However, we can prevent SQL injection using the QUOTENAME() function as shown below.

    Declare @FN nvarchar(50)
    Set @FN = ”’ Drop Database SalesDB –”’
    Declare @sql nvarchar(max)
    Set @sql = ‘Select * from Employees where FirstName = ‘ + QUOTENAME(@FN,””)
    –Print @sql
    Exec(@sql)

    Notice with the quotename function we are using a single quote as a delimiter. With the use of this function if there is a single quote in the user input it is doubled.

    For example, if we set @FN=’John’, notice the string ‘John’ is wrapped in single quotes

    Declare @FN nvarchar(50)
    Set @FN = ‘John’
    Declare @sql nvarchar(max)
    Set @sql = ‘Select * from Employees where FirstName = ‘ + QUOTENAME(@FN,””)
    Print @sql

    When the above query is executed the following is the query printed
    Select * from Employees where FirstName = ‘John’

    Along the same lines, if we try to inject sql, QUOTENAME() function wraps all that input in another pair of single quotes treating it as a value for the FirstName column and prevents SQL injection.

    With sql server auto-parameterisation capability the cached query plans can be reused. SQL Server can detect parameter values and create parameterised queries on its own, even if you don’t explicitly declare them. However, there are exceptions to this. Auto-parameterisation comes in 2 flavours – Simple and Forced. We will discuss auto-parameterisation in detail in a later video.

    Execute the following DBCC command to remove all entries from the plan cache
    DBCC FREEPROCCACHE

    Execute the following query. Notice we have set @FN=’Mary’
    Declare @FN nvarchar(50)
    Set @FN = ‘Mary’
    Declare @sql nvarchar(max)
    Set @sql = ‘Select * from Employees where FirstName = ‘ + QUOTENAME(@FN,””)
    Exec(@sql)

    Execute the following query to retrieve what we have in the query plan cache
    SELECT cp.usecounts, cp.cacheobjtype, cp.objtype, st.text, qp.query_plan
    FROM sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
    CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
    ORDER BY cp.usecounts DESC

    Notice we have an auto-parameterised query and at the moment usecounts is 1.

    Now change @FN=’Mark’ and execute the same query. After the query is completed, retrieve the entries from the plan cache. Notice the usecounts for the auto-parameterised query is 2, suggesting that the same query plan is reused.

    Summary
    1. If you use QUOTENAME() function, you can prevent sql injection while using Exec()
    2. Cached query plan reusability is also not an issue while using Exec(), as SQL server automatically parameterize queries.
    3. I personally prefer using sp_executesql over exec() as we can explicitly parameterise queries instead of relying on sql server auto-parameterisation feature or QUOTENAME() function. I use Exec() only in throw away scripts rather than in production code.

Cách sử dụng hàm lấy giá trị Ở giữa, cách sử dụng hàm mid lấy chuỗi ký tự trong excel

  • Tác giả: modem.vn
  • Đánh giá: 5 ⭐ ( 4124 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Khi xử lý văn bản/ định dạng text trong Excel, chúng ta thường phải xử lý phân chia, cắt chuỗi văn bản,  Trong bài viết này, hoctin

Trích xuất văn bản từ một trường chuỗi nhất định

  • Tác giả: vncodeblog.com
  • Đánh giá: 5 ⭐ ( 7302 lượt đánh giá )
  • Khớp với kết quả tìm kiếm:

Trích xuất văn bản giữa các dấu ngoặc đơn từ chuỗi văn bản

  • Tác giả: vi.extendoffice.com
  • Đánh giá: 5 ⭐ ( 8304 lượt đánh giá )
  • Khớp với kết quả tìm kiếm:

Tổng Hợp Một Số Hàm Cắt Chuỗi Trong Sql Server, Chi Tiết Bài Học 19

  • Tác giả: xechaydiendkbike.vn
  • Đánh giá: 4 ⭐ ( 2004 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Mình làm một bài viết về danh sách các hàm hệ thống xử lý chuỗi trong SQL, Danh sách các hàm xử lý này mọi người có thể tìm trên MSDN hoặc trong phần System Functions trong mỗi database SQL

Cách trích xuất số hoặc văn bản từ Excel

  • Tác giả: mylop.edu.vn
  • Đánh giá: 3 ⭐ ( 6460 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Microsoft Excel rất tuyệt khi làm việc với cả số và văn bản, nhưng nếu sử dụng cả hai trong cùng một ô, bạn có thể gặp một số khó khăn. May mắn thay, bạn có

Hàm xử lý chuỗi trong SQL

  • Tác giả: vietjack.com
  • Đánh giá: 3 ⭐ ( 8830 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Hàm xử lý chuỗi trong SQL – Học SQL cơ bản nâng cao. Học SQL trực tuyến theo các bước từ Khái niệm về database, Cú pháp SQL, Truy vấn SELECT, Truy vấn INSERT, Lệnh DELETE, Truy vấn UPDATE, Ràng buộc (Constraint), Truy vấn DROP, Lệnh TRUNCATE, Từ khóa DISTINCT, Mệnh đề ORDER BY, Mệnh đề GROUP BY, Mệnh đề WHERE, Sử dụng Join, Sử dụng View, Sử dụng Sequence, Transaction, Hàm xử lý, Bảng tạm, Toán tử.

Xem thêm các bài viết khác thuộc chuyên mục: Kiến thức lập trình

Xem Thêm  Kích thước màn hình chung cho thiết kế web đáp ứng - kích thước màn hình đáp ứng css